How to create a SQL query that generates a JSON array of two different objects?

Question

I need to generate a JSON array containing two dissimilar objects:

[
    {
        "command": "setcontext",
        "recordtype": "client",
        "recordid": 1030
    },
    {
        "command": "gotodq",
        "usercode": "react_webuser_debtor_details"
    }
]

I'm able to generate the two objects separate by using:

SELECT 
    'setcontext' AS command, 'client' AS recordtype, 1030 AS recordid 
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

SELECT 
    'gotodq' AS command, 'react_webuser_debtor_details' AS usercode 
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

but I can't find out how to join those two statements into one to get the array.

Obviously I can concatenate both as strings appending the array markers but I'm curious on how a "pure" SQL to JSON solution would look...

Answer 1

In SQL Server 2022 and Azure SQL, you can use the JSON_ARRAY and JSON_OBJECT functions

SELECT JSON_ARRAY(
    JSON_OBJECT(
      'command':'setcontext',
      'recordtype':'client',
      'recordid':1030
    ),
    JSON_OBJECT(
      'command':'gotodq',
      'usercode':'react_webuser_debtor_details'
    )
);

In older versions, you can't use those. Also JSON_ARRAY_AGG also isn't available.

You could just concatenate them. (Use STRING_AGG if you have an undefined number of items.)

SELECT '[' + (
    SELECT 
      'setcontext' AS command, 'client' AS recordtype, 1030 AS recordid 
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ) + ',' + (
    SELECT 
      'gotodq' AS command, 'react_webuser_debtor_details' AS usercode 
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  );

Or you could do it as a UNION ALL, but then columns with the same name would need to be the same data type.

SELECT 
  'setcontext' AS command,
  'client' AS recordtype,
  1030 AS recordid,
  NULL AS usercode

UNION ALL

SELECT 
    'gotodq',
    NULL,
    NULL,
    'react_webuser_debtor_details'

FOR JSON PATH;

db<>fiddle