Question 200997 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

JSON PATH – how to create multiple array elements on the fly

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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search