CODEWITHSUNDEEP
sqljsonsql-server
Francis Ducharme
Francis Ducharme

Reputation: 4987

Join a JSON array's property back into one value

I have a table with an nvarchar column that contains a serialized JSON array.

What I'm trying to do is parse that array and join the value of a property back into one string.

Here's the column content:

enter image description here

And in my final query, I'd like it come be displayed as one value like "Videowall" in "MergedValues". Obviously, if the array has many items, I'd like to see "Videowall, Factory, Car", etc.

Here's what I tried so far

SELECT *,
      (SELECT Name
         FROM OPENJSON(JsonColumn)
         WITH 
         (
          Id   VARCHAR(200) '$.Id',
          Name VARCHAR(200) '$.Name'
         )) 
  FROM MyTable
 WHERE JsonColumn <> '[]'

This obvious error comes up

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I think it's the joining of the many parsed results that's missing.

Upvotes: 0

Views: 392

Answers (2)

Zhorov
Zhorov

Reputation: 29943

You need to aggregate the results from the OPENJSON() call for each row:

Table:

SELECT *
INTO Data
FROM (VALUES
   (0, 0, 25200, '[{"Id":"10564", "Name": "Videowall"}]'),
   (0, 0, 9200, '[{"Id":"10564", "Name": "Videowall"},{"Id":"10565", "Name": "Factory"}]')
) v (OriginalEstimateSeconds, RemainingEstimateSeconds, TimeSpentSeconds, ComponentsJSON)

Statement:

SELECT
   OriginalEstimateSeconds, RemainingEstimateSeconds, TimeSpentSeconds, ComponentsJSON,
   MergedValues = (
      SELECT STRING_AGG([Name], ',')
      FROM OPENJSON(ComponentsJSON) WITH (Name varchar(100) '$.Name')
   )
FROM Data

Result:

... ComponentsJSON                                                          MergedValues
---------------------------------------------------------------------------------------------
... [{"Id":"10564", "Name": "Videowall"}]                                   Videowall
... [{"Id":"10564", "Name": "Videowall"},{"Id":"10565", "Name": "Factory"}] Videowall,Factory

If the order of the $.Name values in the newly generated text is important, you may use the script below:

SELECT
   OriginalEstimateSeconds, RemainingEstimateSeconds, TimeSpentSeconds, ComponentsJSON,
   MergedValues = (
      SELECT STRING_AGG(JSON_VALUE([value], '$.Name'), ',') WITHIN GROUP (ORDER BY CONVERT(int, [key]))
      FROM OPENJSON(ComponentsJSON)
   )
FROM Data

Upvotes: 1

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65218

A Subquery is not needed, but CROSS APPLY should be used to join the table and OPENJSON expression, and use STRING_AGG() at the end in order to aggregate the extracted names such as

SELECT JsonColumn, STRING_AGG(Name, ',') AS [Merged Values]
  FROM MyTable
 CROSS APPLY OPENJSON(JsonColumn)
  WITH 
  (
   Id   VARCHAR(200) '$.Id',
   Name VARCHAR(200) '$.Name'
  )
 GROUP BY JsonColumn

Demo

Upvotes: 1

Related Questions