Complex JSON using JSON_MODIFY without nested arrays or escape characters (WITHOUT_ARRAY_WRAPPER)

Question

I am using JSON_MODIFY to build complex JSON. Moving from MySQL I am struggling with the JSON functions provided by SQL Server. The issue I'm having is that SQL Server seems to construct all JSON objects in an array. There is the WITHOUT_ARRAY_WRAPPER mechanism, which seems like it should do what I want, however; there are two undesirable consequences.

It only returns one result depending on how it is used
The result is a single string with escape characters

I have constructed a simple query which illustrates my needs and the issue.

QUERY 1

SELECT JSON_MODIFY(
    JSON_QUERY('{"definitions": {"id": "INT", "name": "VARCHAR(23)"}}'),
    'append $.data',
    (
        SELECT * FROM (
            SELECT 1 AS id, '123abc' AS "name" UNION
            SELECT 2 AS id, '234bcd' AS "name"
        ) AS "data"
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER 
    ) 
) AS "data";

OUTPUT 1

{
   "definitions":{
      "id":"INT",
      "name":"VARCHAR(23)"
   },
   "data":[
      "{\"id\":1,\"name\":\"123abc\"},{\"id\":2,\"name\":\"234bcd\"}"
   ]
}

QUERY 2

SELECT JSON_MODIFY(
    JSON_QUERY('{"definitions": {"id": "INT", "name": "VARCHAR(23)"}}'),
    'append $.data',
    (
        SELECT * FROM (
            SELECT 1 AS id, '123abc' AS "name" UNION
            SELECT 2 AS id, '234bcd' AS "name"
        ) AS "data"
        FOR JSON PATH
    ) 
) AS "data";

OUTPUT 2

{
   "definitions":{
      "id":"INT",
      "name":"VARCHAR(23)"
   },
   "data":[
      [
         {"id":1, "name":"123abc"},
         {"id":2, "name":"234bcd"}
      ]
   ]
}

QUERY 1 The data object is an array (which is expected), but the problem is what is in the array... A single string with escape characters.
QUERY 2 The data object is an array, which contains an array. In order to access the actual array of data, I would use something like for each obj in data[0].... The problem this poses is, for anyone consuming the JSON object, I would have to tell them:

"In this particular object the data element is an array of arrays--You'll want to use the first and only the first element to access the actual array of data."

I've naively tried many different combinations of JSON_MODIFY, JSON_QUERY, and CONCAT to no avail. How can I properly use JSON_MODIFY to get the following output, without the double array in data?

{
   "definitions":{
      "id":"INT",
      "name":"VARCHAR(23)"
   },
   "data":[
     {"id":1, "name":"123abc"},
     {"id":2, "name":"234bcd"}
   ]
}

Charlieface · Accepted Answer · 2021-07-19 23:39:47Z

1

You are over-thinking this by trying to JSON_MODIFY an existing object.

Construct the definitions and data properties that you need, inside a subquery if necessary.

Then use FOR JSON a second time to get the outer object.

SELECT 
    definitions = JSON_QUERY('{"id": "INT", "name": "VARCHAR(23)"}'),
    data = 
    (
        SELECT id, name
        FROM (VALUES
            (1, '123abc'),
            (2, '234bcd')
        ) v(id, name)
        FOR JSON PATH
    )
FOR JSON PATH;

SQL Fiddle

answered Jul 19, 2021 at 23:39

Charlieface

78.5k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mark Davich · Accepted Answer · 2021-07-19 21:12:29Z

0

By trial and error, I found the solution.

Removed the append keyword from the path parameter in the JSON_MODIFY statement
Removed the WITHOUT_ARRAY_WRAPPER parameter from the FOR JSON statement.

Now the results are as expected and I don't need to explain to any consumers to "Just use data[0]"

The Query

SELECT JSON_MODIFY(
    JSON_QUERY('{"definitions": {"id": "INT", "name": "VARCHAR(23)"}}'),
    '$.data',
    (
        SELECT * FROM (
            SELECT 1 AS id, '123abc' AS "name" UNION
            SELECT 2 AS id, '234bcd' AS "name"
        ) AS "data"
        FOR JSON PATH
    ) 
) AS "data";

Produces the following output

{
   "definitions":{
      "id":"INT",
      "name":"VARCHAR(23)"
   },
   "data":[
     {"id":1, "name":"123abc"},
     {"id":2, "name":"234bcd"}
   ]
}

edited Jul 19, 2021 at 21:12

answered Jul 19, 2021 at 20:52

Mark Davich

6381 gold badge6 silver badges23 bronze badges

Collectives™ on Stack Overflow

Complex JSON using JSON_MODIFY without nested arrays or escape characters (WITHOUT_ARRAY_WRAPPER)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related