CODEWITHSUNDEEP
jsonsql-server
Ralf de Kleine
Ralf de Kleine

Reputation: 11744

Using JSON_VALUE + JSON_QUERY to create new JSON

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:

And would like to return:

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

Upvotes: 0

Views: 282

Answers (2)

Zhorov
Zhorov

Reputation: 29993

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

Upvotes: 3

Salman Arshad
Salman Arshad

Reputation: 272256

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.

Upvotes: 2

Related Questions