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

zzwlnbp8 于 6个月前发布在其他

关注(0)|答案(1)|浏览(67)

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

sql-server

来源：https://stackoverflow.com/questions/77718055/how-to-create-a-sql-query-that-generates-a-json-array-of-two-different-objects

1条答案

按热度按时间

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

赞(0）回复(0）举报 6个月前

我来回答

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

1条答案

相关问题

热门标签

最新问答