Using JSON_VALUE + JSON_QUERY to create new JSON

Question

I was wondering if it would be possible to reshape JSON and return it as JSON. I have JSON which, in simplified form, looks like:

Name
Details
- fieldId
- fieldValue
Other
- Id
- Value

And would like to return:

Name
Details
- fieldId
- fieldValue

I can return Name and Details with JSON_VALUE and JSON_QUERY but would like it as one combined JSON field.

create table #test (
  [id] int,
  [json] varchar(max)
);

insert into #test (id, json) values (1, '{
  "Name": "Test 1",
  "Details": [
    {
      "fieldId": "100",
      "fieldValue": "ABC"
    }],
  "Other": [
    {
      "Id": "1",
      "Value": "ABC"
    }]
}');

insert into #test (id, json) values (2, '{
  "Name": "Test 2",
  "Details": [
    {
      "fieldId": "101",
      "fieldValue": "ABCD"
    }],
  "Other": [
    {
      "Id": "2",
      "Value": "ABCD"
    }]
}');

select id, JSON_VALUE(json, '$.Name'), JSON_QUERY(json, '$.Details')
from #test

Zhorov · Accepted Answer · 2021-07-08 12:21:22Z

3

As an additional option, you may parse the JSON with OPENJSON() and explicit schema (columns definitions) and then build the new JSON again:

SELECT
   id,
   newjson = (
      SELECT Name, Details 
      FROM OPENJSON(json) WITH (
         Name varchar(max) '$.Name',
         Details nvarchar(max) '$.Details' AS JSON
      )
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
   )
FROM #test

And the same approach with JSON_VALUE() and JSON_QUERY():

SELECT 
  id, 
  newjson = (
     SELECT JSON_VALUE(json, '$.Name') AS [Name], JSON_QUERY(json, '$.Details') AS [Details]
     FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  )   
FROM #test

edited Jul 8, 2021 at 12:21

answered Jul 8, 2021 at 10:39

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Salman Arshad · Accepted Answer · 2021-07-08 09:44:17Z

2

One solution is to use JSON_MODIFY to re-construct the JSON:

SELECT
    id,
    JSON_MODIFY(
        JSON_MODIFY(
            '{}',
            '$.Name',
            JSON_VALUE(json, '$.Name')
        ),
        '$.Details',
        JSON_QUERY(json, '$.Details')
    ) AS new_json
FROM #test

An alternate would be to delete the Other node using JSON_MODIFY but you have to know the name of node(s) to remove.

answered Jul 8, 2021 at 9:44

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Collectives™ on Stack Overflow

Using JSON_VALUE + JSON_QUERY to create new JSON

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related