CODEWITHSUNDEEP
sqljsonsql-server
PierekEast
PierekEast

Reputation: 95

Create one json per one table row

I would like to create jsons from the data in the table. Table looks like that:

|code  |
+------+
|D5ABX0|
|MKT536|
|WAEX44|

I am using FOR JSON PATH which is nice:

SELECT [code]
FROM feature
FOR JSON PATH

but the return value of this query are three concatenated jsons in one row:

   |JSON_F52E2B61-18A1-11d1-B105-00805F49916B              |
+----------------------------------------------------------+
1  |[{"code":"D5ABX0"},{"code":"MKT536"},{"code":"WAEX44"}]|

I need to have each row to be a separate json, like that:

   |JSON_return       |
+---------------------+
1  |{"code":"D5ABX0"} |
2  |{"code":"MKT536"} |
3  |{"code":"WAEX44"} |

I was trying to use splitting function (CROSS APPLY) which needs to have a separator as a parameter but this is not a robust solution as the json could be more expanded or branched and this could separate not the whole json but the json inside the json:

;WITH split AS (
SELECT [json] = (SELECT code FROM feature FOR JSON PATH)
)
SELECT
     T.StringElement
FROM split S
CROSS APPLY dbo.fnSplitDelimitedList([json], '},{') T

The output is:

   |StringElement     |
+---------------------+
1  |[{"code":"D5ABX0" |
2  |"code":"MKT536"   |
3  |"code":"WAEX44"}] |

Is there a way to force sqlserver to create one json per row?

Upvotes: 7

Views: 8553

Answers (1)

Thom A
Thom A

Reputation: 95544

You'll need to use as subquery to achieve this; FOR JSON will create a JSON string for the entire returned dataset. This should get you what you're after:

CREATE TABLE #Sample (code varchar(6));
INSERT INTO #Sample
VALUES ('D5ABX0'),
       ('MKT536'),
       ('WAEX44');

SELECT (SELECT Code
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
FROM #Sample S;
DROP TABLE #Sample;

Upvotes: 17

Related Questions