Joining multiple jsonblob rows to return a JSON

Question

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}]

What you describe has nothing to do with JOINs. Frankly, it looks like the table needs a drastic redesign. What are those values and why aren't they stored in a separate table? Or as a geometry type? If this is a MULTIPOINT or LINESTRING, you could use UnionAggregate to combine the points — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 2, 2022 at 8:00
If you really have to deal with JSON arrays, you first need to extract the values with OPENJSON then combine the result back into a JSON string with FOR JSON — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 2, 2022 at 8:02
@PanagiotisKanavos this is data from a graph. they are read over a long period of time and we save data every one second. for every save im creating a new row. (that can have one - 20 datapoints) . but later on , i need to get all the data points for a given type. — mmek123
– mmek123, Commented Mar 2, 2022 at 21:26
That doesn't change any of the comments. You can store this as a MULTIPOINT in a geometry column and even add spatial indexes if needed. If you want to keep using JSON, you'll have to parse the content and generate a new JSON string as Zhorov shows — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 3, 2022 at 8:27

Zhorov · Accepted Answer · 2022-03-03 09:01:14Z

1

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}]

edited Mar 3, 2022 at 9:01

answered Mar 2, 2022 at 8:16

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

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

2 Comments

mmek123 Over a year ago

May i know what does , CONVERT(int, j.[key]) this part do ?

Zhorov Over a year ago

OPENJSON with default schema returns a table with columns key, value and type and when the parsed JSON is a JSON array, the key column holds a 0-based index of the element in the specified array as an nvarchar(4000) value. CONVERT(int, j.[key]) simply returns the JSON array items in the original order.

Collectives™ on Stack Overflow

Joining multiple jsonblob rows to return a JSON

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related