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

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.

CodePudding user response：

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.

CodePudding user response：

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;