SQL Server JSON PATH - how to create multiple array elements on the fly

bqucvtff 于 2023-08-02 发布在其他

关注(0)|答案(2)|浏览(93)

I am using SQL Server 'JSON PATH' functions to create nested JSON strings programmatically - without actually querying the database, since the strings placeholders (##..##) will undergo further processing elsewhere. This works fine so far with this type of query:

SELECT 
'##ORDER_ID##' AS 'order.id',  
'##ORDER_ACCOUNT##' AS 'order.account',
(
SELECT 
'##ITEM_NUM_01##' AS 'partNumber',
'##ITEM_UNIT_PRICE_01##' AS 'unitPrice'
FOR JSON PATH
) AS 'order.items'
FOR JSON PATH

This correctly gives me:

[{
    "order": {
        "id": "##ORDER_ID##",
        "account": "##ORDER_ACCOUNT##",
        "items": [{
            "partNumber": "##ITEM_NUM_01##",
            "unitPrice": "##ITEM_UNIT_PRICE_01##"
        }]
    }
}]

But how can I create additional elements in the "items" array?

Any attempts to modify the SELECT statement to get something like the following have not been succesful so far:

[{
    "order": {
        "id": "##ORDER_ID##",
        "account": "##ORDER_ACCOUNT##",
        "items": [{
                "partNumber": "##ITEM_NUM_01##",
                "unitPrice": "##ITEM_UNIT_PRICE_01##"
            },
            {
                "partNumber": "##ITEM_NUM_02##",
                "unitPrice": "##ITEM_UNIT_PRICE_02##"
            }
        ]
    }
}]

I am quite versed with ordinary SQL syntax and tried to use UNION and subqueries etc. - but this didn't work since it created all kinds of syntax errors and incompatibilites.

I simply want to populate arrays in the resulting JSON string with more than one element - what am I missing here?

sql-server

来源：https://stackoverflow.com/questions/76619200/json-path-how-to-create-multiple-array-elements-on-the-fly

2条答案

按热度按时间

9rnv2umw1#

You can use union all - but it requires wrapping it in a subquery for JSON PATH to work:

SELECT 
    '##ORDER_ID##' AS [order.id],  
    '##ORDER_ACCOUNT##' AS [order.account],
    (
        SELECT *
        FROM (
            SELECT '##ITEM_NUM_01##' AS [partNumber], '##ITEM_UNIT_PRICE_01##' AS [unitPrice]
            UNION ALL
            SELECT '##ITEM_NUM_02##', '##ITEM_UNIT_PRICE_02##'
        ) x
        FOR JSON PATH
    ) AS [order.items]
FOR JSON PATH

Or you can use row-constructor VALUES() :

SELECT 
    '##ORDER_ID##' AS [order.id],  
    '##ORDER_ACCOUNT##' AS [order.account],
    (
        SELECT *
        FROM (VALUES ('##ITEM_NUM_01##', '##ITEM_UNIT_PRICE_01##'),
                     ('##ITEM_NUM_02##', '##ITEM_UNIT_PRICE_02##')
              ) AS v([partNumber], [unitPrice])
        FOR JSON PATH
    ) AS [order.items]
FOR JSON PATH

Note that this uses square brackets to quote the identifiers ( [] ) rather than single quotes ( '' ), which should in general be reserved to literal strings (although SQL Server accepts otherwise).

Demo on DB Fiddle

赞(0）回复(0）举报 2023-08-02

cwxwcias2#

If you really ...create nested JSON strings programmatically without actually querying the database..., an additional option introduced in SQL Server 2022 is a combination of JSON_ARRAY() and JSON_OBJECT() functions:

SELECT JSON_ARRAY(JSON_OBJECT('order' : JSON_OBJECT(
   'id': '##ORDER_ID##',
   'account': '##ORDER_ACCOUNT##',
   'items': JSON_ARRAY(
      JSON_OBJECT('partNumber': '##ITEM_NUM_01##', 'unitPrice': '##ITEM_UNIT_PRICE_01##'),
      JSON_OBJECT('partNumber': '##ITEM_NUM_02##', 'unitPrice': '##ITEM_UNIT_PRICE_02##')
   )
)))

For earlier versions you may try to construct the inner JSON content using VALUES table value constructor (as in the @GMB's answer):

SELECT 
   [order.id] = '##ORDER_ID##',
   [order.account] = '##ORDER_ACCOUNT##',
   [order.items] = (
      SELECT partNumber, unitPrice
      FROM (VALUES
         ('##ITEM_NUM_01##', '##ITEM_UNIT_PRICE_01##'),
         ('##ITEM_NUM_02##', '##ITEM_UNIT_PRICE_02##')
      ) v (partNumber, unitPrice)
      FOR JSON PATH
   )
FOR JSON PATH

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server JSON PATH - how to create multiple array elements on the fly

2条答案

相关问题

热门标签

最新问答