Convert property from JSON object array to comma-delimited string as column of result set?

Question

From this code example...

DROP TABLE IF EXISTS dbo.JsonTest;
GO

CREATE TABLE [dbo].[JsonTest](
       [JsonTestId] [int] IDENTITY(1,1) NOT NULL,
       [JsonContent] [nvarchar](max) NULL,
       [FirstName]  AS (json_value([JsonContent],'$.firstName')) PERSISTED,
       [LastName]  AS (json_value([JsonContent],'$.lastName')) PERSISTED,
CONSTRAINT [PK_JsonTest] PRIMARY KEY CLUSTERED 
(
       [JsonTestId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

INSERT INTO dbo.JsonTest (JsonContent)
VALUES ('{ "firstName": "foo", "lastName": "oof", "fields": [ { "fieldId": 1, "fieldName": "Field 1"}, { "fieldId": 2, "fieldName": "Field 2"} ] }');

INSERT INTO dbo.JsonTest (JsonContent)
VALUES ('{ "firstName": "bar", "lastName": "rab", "fields": [ { "fieldId": 3, "fieldName": "Field 3"}, { "fieldId": 4, "fieldName": "Field 4"} ] }');
GO

SELECT
       *
       ,JSON_QUERY(JsonContent,'$.fields') AS FieldArray
FROM dbo.JsonTest;
GO

I am trying to get this...

Desired Output:

I was able to get FirstName and LastName from the JSON as computed columns, but I now need to get a comma delimited string of the fieldName property within the fields array on each row.

I'm guessing this may have to be through a query and may not be possible through a computed column, but a computed column would be ideal, if at all possible.

I've tried lots of experiments with JSON_QUERY and STRING_AGG, but the closest I can get is shown in the final query in the code above, where I am able to emit the entire JSON array. But, I still cannot for the life of me figure out how to get a comma-delimited string.

This needs to be performant at scale for the entire table, since the parsed values will be used for filtering full table queries.

Thanks in advance.

Answer 1

I'm not sure if the computed columns will help you much with performance. Consider a view instead, eg

WITH cte AS (
SELECT
    JsonTestId,
    JSON_VALUE( JsonContent, '$.firstName' ) firstName,
    JSON_VALUE( JsonContent, '$.lastName' ) lastName,
    JSON_VALUE( f.value, '$.fieldName' ) fieldName
FROM dbo.JsonTest m
    CROSS APPLY OPENJSON( JsonContent, '$.fields') f
) 
SELECT JsonTestId, STRING_AGG( fieldName, ', ' ) fields
FROM cte
GROUP BY JsonTestId;

Otherwise, Zhorov's answer is excellent and should be promoted to a full answer.

Answer 2

Answer by @Zhorov

SELECT
  jt.JsonTestId,
  jt.FirstName,
  jt.LastName,
  FieldArray = (
    SELECT
      STRING_AGG(JSON_VALUE(f.[value], '$.fieldName'), ',')
         WITHIN GROUP (ORDER BY CONVERT(int, f.[key]))
    FROM OPENJSON(JsonContent, '$.fields') f
  )
FROM dbo.JsonTest jt;