Output JSON with multiple arrays inside

Question

I am trying to output the query to JSON format with multiple arrays, but couldn't make it works correctly. I ran into this link, but it is not quite the output I wanted.

Here are the sample tables:

DECLARE @Persons AS TABLE
(
    person_id int primary key,
    person_name varchar(20)
)

DECLARE @Pets AS TABLE
(
    pet_owner int, -- in real tables, this would be a foreign key to Person.person_id
    pet_id int  primary key,
    pet_name varchar(10)
)

INSERT INTO @Persons (person_id, person_name) VALUES
(2, 'Jack'),
(3, 'Jill')

INSERT INTO @Pets (pet_owner, pet_id, pet_name) VALUES
(2, 4, 'Bug'),
(2, 5, 'Feature'),
(3, 6, 'Fiend')

The JSON output that I want to produce this like this below:

[{
    "2" : [{
            "pet_id" : 4,
            "name" : "Bug",
            },
            {
            "pet_id" : 5,
            "name" : "Feature",
            }
        ],
    "3" : [{
            "pet_id" : 6,
            "name" : "Fiend",
            }
        ]
}]

If regular built-in FOR JSON Path option is not feasible, I also take any tricks like concatenation, etc.

Zhorov · Accepted Answer · 2020-01-27T18:38:28.317

I don't think that you can generate JSON output with variable key names using only FOR JSON AUTO / FOR JSON PATH.

For SQL Server 2016+ one possible solution is to use FOR JSON AUTO and FOR XML PATH.

For SQL Server 2017+, you may use only JSON functions to generate the expected output, starting from JSON array, containing one empty JSON object as first item. With JSON_MODIFY() and lax mode by default, the JSON output generated from FOR JSON AUTO is inserted as value for the key, specified by CONCAT('$[0]."', person_id, '"') expresssion (using expression as value for the path parameter is supported from SQL Server 2017). To be honest, JSON_QUERY() is not needed here, but as a rule, JSON_MODIFY() escapes all special characters in the new value. In this case you need to get a properly formatted JSON using FOR JSON, JSON_QUERY() or JSON_MODIFY() (JSON_QUERY(person_json, '$') returns the whole JSON).

Tables:

DECLARE @Persons AS TABLE (
    person_id int primary key,
    person_name varchar(20)
)
DECLARE @Pets AS TABLE (
   pet_owner int, -- in real tables, this would be a foreign key to Person.person_id
   pet_id int  primary key,
   pet_name varchar(10)
)
INSERT INTO @Persons 
   (person_id, person_name) 
VALUES
   (2, 'Jack'),
   (3, 'Jill')
INSERT INTO @Pets 
   (pet_owner, pet_id, pet_name) 
VALUES
   (2, 4, 'Bug'),
   (2, 5, 'Feature'),
   (3, 6, 'Fiend')

Statement (SQL Server 2016+):

SELECT CONCAT(
   '[{',
   STUFF((
      SELECT CONCAT(',"', person_id, '":', person_json)
      FROM @Persons p
      CROSS APPLY (
         SELECT pet_id, pet_name AS name
         FROM @Pets
         WHERE pet_owner = p.person_id
         FOR JSON AUTO
      ) j (person_json)
      FOR XML PATH('') 
      ), 1, 1, ''),
  '}]'
  )

Statement (SQL Server 2017+):

DECLARE @json varchar(max) = '[{}]'

SELECT @json = JSON_MODIFY(
                  @json, 
                  CONCAT('$[0]."', person_id, '"'), 
                  --JSON_QUERY(person_json, '$')
                  person_json    
               )
FROM @Persons p
CROSS APPLY (
   SELECT pet_id, pet_name AS name
   FROM @Pets
   WHERE pet_owner = p.person_id
   FOR JSON AUTO
) j (person_json)

SELECT @json

Result:

[{"2":[{"pet_id":4,"name":"Bug"},{"pet_id":5,"name":"Feature"}],"3":[{"pet_id":6,"name":"Fiend"}]}]

It works. Thank you so much. If you don't mind, can you please explain the 2nd solution where you have `JSON_MODIFY`, `JSON_QUERY`. What is the `$` means? — Milacay, Jan 27 '20 at 17:18
I am still learning and really appreciated your time explaining. Thanks again! — Milacay, Jan 27 '20 at 21:03
last question. At the end of Cross Apply statement, it has the derived table named `j (person_json)`. I understand the `j` which I normally used for Cross Apply, but have never seen ` (person_json)' 2nd part. Are you stored it in a variable to use later in the JSON_Modify? — Milacay, Jan 27 '20 at 23:03
@Milacay `person_json` is a column alias for the column in `j` derived table. — Zhorov, Jan 28 '20 at 06:40

Output JSON with multiple arrays inside

1 Answers1