Joining multiple jsonblob rows to return a JSON-CodePudding

My table structure is as below.

I want to return the data column but joined together as a JSON where type = 1 and order by the time column. I used STRING_AGG(), but its not what I want since I need to serialize this in the other side.

 | id | type |data                             | time              |
 | ---|----- | ------------------------------- |-------------------|
 | 1  | 1    |[{"X":55,"Y":97}]                |2022-03-02 17:20:21|
 | 2  | 1    |[{"X":3,"Y":6},{"X":39,"Y":9}]   |2022-03-02 17:20:25|
 | 3  | 5    |[{"X":9,"Y":9},{"X":33,"Y":1}]   |2022-03-02 17:20:29|

Basically I need to return [{"X":55,"Y":97},{"X":3,"Y":6},{"X":39,"Y":9}]

CodePudding user response：

A possible approach is to parse the stored data as JSON, filter and order the returned data, and build the expected data as JSON:

Table:

SELECT *
INTO Data 
FROM (VALUES
   (1, 1, '[{"X":55,"Y":97}]',              CONVERT(datetime2(0), '2022-03-02T17:20:21')),
   (2, 1, '[{"X":3,"Y":6},{"X":39,"Y":9}]', CONVERT(datetime2(0), '2022-03-02T17:20:25')),
   (3, 5, '[{"X":9,"Y":9},{"X":33,"Y":1}]', CONVERT(datetime2(0), '2022-03-02T17:20:29'))
) d (id, [type], data, time)

Statement:

SELECT 
   X = CONVERT(int, JSON_VALUE(j.[value], '$.X')),
   Y = CONVERT(int, JSON_VALUE(j.[value], '$.Y'))
FROM Data d
CROSS APPLY OPENJSON(d.data) j
WHERE d.type = 1
ORDER BY d.time, CONVERT(int, j.[key])
FOR JSON AUTO

Result:

[{"X":55,"Y":97},{"X":3,"Y":6},{"X":39,"Y":9}]