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

zzwlnbp8  于 11个月前  发布在  其他
关注(0)|答案(1)|浏览(95)

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...

gab6jxml

gab6jxml1#

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

相关问题