How to change a column of JSON array of objects to object in SQL Server

Question

I am working on a SQL table with a column with JSON value. Each row of that column is string value in JSON structure. This JSON structure is always one array that contains one or more objects of one item. The number and key words of objects can vary. For instance, a first row might look like this:

[{"Page View":"Page"}
,{"Search Data":"9"}
,{"Search Distance":"undefined"}
,{"Search Location":"undefined"}
,{"Search Filters":"{}"}
,{"Search No Restrictions":"undefined"}
,{"Search Term":"Services"}
,{"Search Type":"Id"}]

The second row value might look like this:

[{"Page Type":"Service"}
,{"Organization ID":"111555666"}
,{"Service ID":"333444"}
,{"refUrl":"https://randomURL"}]

I am trying to convert these values to be one object with multiple elements

So the first row looks like this:

{"Page View":"Page"
,"Search Data":"9"
,"Search Distance":"undefined"
,"Search Location":"undefined"
,"Search Filters":"{}"
,"Search No Restrictions":"undefined"
,"Search Term":"Services"
,"Search Type":"Id"}

And the second row looks like this:

{"Page Type":"Service"
,"Organization ID":"111555666"
,"Service ID":"333444"
,"refUrl":"https://randomURL"}

I tried this method:

SELECT FRUA.Id,
    REPLACE(REPLACE(REPLACE(REPLACE(JSON_column, '{',''),'}',''), '[','{'),']','}')
FROM test.table

This works, but it can change unintended { or [ in value like "Search Filters":"{}", or can wreck a nested element. Is there a better way to accomplish this on SQL Server Azure 12.0.2000.8?

Answer 1

It's an unnamed JSON array of JSON objects. To access the elements of the array the answer uses JSON_QUERY and column offset. Once the JSON objects have been extracted from the array into columns, the solution uses JSON_VALUE to extract the field values. Once the field values have extracted into columns, the resulting table is serialized using FOR JSON PATH and specifies WITHOUT_ARRAY_WRAPPER.

JSON Data

declare @json           nvarchar(max)=
N'[{"Page View":"Page"}
  ,{"Search Data":"9"}
  ,{"Search Distance":"undefined"}
  ,{"Search Location":"undefined"}
  ,{"Search Filters":"{}"}
  ,{"Search No Restrictions":"undefined"}
  ,{"Search Term":"Services"}
  ,{"Search Type":"Id"}]';

Query

with j_cte as (
    select
       json_query(@json, '$[0]') AS a,
       json_query(@json, '$[1]') AS b,
       json_query(@json, '$[2]') AS c,
       json_query(@json, '$[3]') AS d,
       json_query(@json, '$[4]') AS e,
       json_query(@json, '$[5]') AS f,
       json_query(@json, '$[6]') AS g,
       json_query(@json, '$[7]') AS h )
select json_value(jc.a, N'$."Page View"') AS [Page View],
       json_value(jc.b, N'$."Search Data"') AS [Search Data],
       json_value(jc.c, N'$."Search Distance"') AS [Search Distance],
       json_value(jc.d, N'$."Search Location"') AS [Search Location],
       json_value(jc.e, N'$."Search Filters"') AS [Search Filters],
       json_value(jc.f, N'$."Search No Restrictions"') AS [Search No Restrictions],
       json_value(jc.g, N'$."Search Term"') AS [Search Term],
       json_value(jc.h, N'$."Search Type"') AS [Search Type]
from j_cte jc for json path, without_array_wrapper;

Output

{
  "Page View": "Page",
  "Search Data": "9",
  "Search Distance": "undefined",
  "Search Location": "undefined",
  "Search Filters": "{}",
  "Search No Restrictions": "undefined",
  "Search Term": "Services",
  "Search Type": "Id"
}

Answer 2

One possible solution is to use OPENJSON() to extract each JSON object from the stored JSON array and SUSBTRING() and STRING_AGG() to build the final output:

Table:

CREATE TABLE Data (JsonData varchar(1000))
INSERT INTO Data (JsonData)
VALUES
   ('[{"Page View":"Page"}
,{"Search Data":"9"}
,{"Search Distance":"undefined"}
,{"Search Location":"undefined"}
,{"Search Filters":"{}"}
,{"Search No Restrictions":"undefined"}
,{"Search Term":"Services"}
,{"Search Type":"Id"}]'),
   ('[{"Page Type":"Service"}
,{"Organization ID":"111555666"}
,{"Service ID":"333444"}
,{"refUrl":"https://randomURL"}]')

Table:

UPDATE Data
SET JsonData = (
   SELECT CONCAT('{', STRING_AGG(SUBSTRING([value], 2, LEN([value]) - 2), ','), '}')
   FROM OPENJSON(JsonData)
)

Result:

JsonData
{"Page View":"Page","Search Data":"9","Search Distance":"undefined","Search Location":"undefined","Search Filters":"{}","Search No Restrictions":"undefined","Search Term":"Services","Search Type":"Id"}
{"Page Type":"Service","Organization ID":"111555666","Service ID":"333444","refUrl":"https://randomURL"}