How to work with relational <-> json data?

Help JSON
1

I’m having trouble to getting JSON to work in SIngleStore DB. I’m using MSSQL for my project and I’m trying to translate it to SingleStore.

Consider this table:

CREATE TABLE dbo.Categories (
	id int NOT NULL,
	parentId int NULL,
	active bit NOT NULL,
	name nvarchar(32) NOT NULL
);

It’s an hierarchical categories table (so 1 - Cars can have as child: 11 - Used, 12 - New, for example).

Using this T-SQL query:

SELECT pc.id, pc.name,
(
	SELECT sc.id, sc.name
	FROM Categories AS sc
	WHERE sc.parentId = pc.id AND sc.active = 1
	ORDER BY name
	FOR JSON PATH
) AS categories
FROM Categories AS pc
WHERE pc.parentId IS NULL AND pc.active = 1
ORDER BY id
FOR JSON PATH

I get this result in one TEXT column:

[ 
  {"id": 1, "name": "Cars", "categories": [
    {"id": 11, "name": "Used"},
    {"id": 12, "name": "New"}
  ]}
]

NOTICE that is an hierarchical JSON generated from multiple tables (one in this case, but I can nest as many SELECT statements as sub-queries, as long they are FOR JSON PATH or FOR JSON AUTO). The TO_SQL(*) seems to work only with a single set of rows from one and only one table =\

So I can get a full database graph converted to a single JSON without too much trouble.

How can I do the same in SingleStore?

For reading JSON, I use this:

INSERT INTO Categories(id, name)
(
  SELECT OPENJSON(@JSON) WITH (
    'id' VARCHAR(32) '$.id',
    'name' NVARCHAR(128) '$.name'
    'parentId' VARCHAR(32) '$.parentId'
  ) 
  WHERE parentId IS NULL
)

When using arrays, I can use CROSS APPLY to mix JSON, for example:

SELECT SalesOrderID, OrderDate, value AS Reason  
FROM Sales.SalesOrderHeader  
     CROSS APPLY OPENJSON (SalesReasons) WITH (value NVARCHAR(100) '$')

So I can use a JSON as a table (meaning: I’m able to JOIN it, select from it, filter from it, etc.).

Most of the times, my stored procedures read data from a JSON argument:

CREATE PROCEDURE FooBar(@JSON NVARCHAR(MAX)) AS BEGIN
  DECLARE @valueA VARCHAR(32);
  DECLARE @valueB INT;
  DECLARE @valueC DATETIMEOFFSET(2);

  SELECT @valueA = string, @valueB = integer, @valueC = iso_date FROM
  OPENJSON(@JSON) WITH (
    string VARCHAR(32) '$.string',
    integer INT '$.integer',
    iso_date DATETIMEOFFSET(2) '$.iso_date'
  );

  SELECT @valueA, @valueB, @valueC;
END

If I execute this proc:

EXEC FooBar @JSON = '{"string": "Teste", "integer": 42, "iso_date": "2021-01-04T21:32:00Z"}';

I get this:

+-------+----+----------------------------------+
| Teste | 42 | January 4, 2021, 09:32:00 PM GMT |
+-------+----+----------------------------------+

How can I do such JSON read/write with SingleStore?

2

Hi jckodel,

Thank you for reaching out, and welcome to our community.

I want to clarify what you are looking for and make some suggestions based on my understanding.

You have multiple tables that contain data with a hierarchical relationship between them. You want 1 nested JSON object summarizing the “master” attribute (e.g., cars) and all the “child” details (e.g., used or new and other attributes).

If this is the case, would JSON_AGG meet your need here (released in 7.3)?

Specifically, one example provided in the documentation shows how to represent a master-detail relationship in a single JSON object, which I believe is similar to what you are trying to do. Please review this example linked and let me know if my understanding is correct on your goal. Will this example and the JSON_AGG function help you create the query & results you are looking for?

Related to JSON_AGG, check out our other JSON functions, like TO_JSON that may help you with your queries as well.

Best,
Roxanna

3

Could still not make it to work. Instructions are VERY unclear in the documentation.

Using the provided documentation, adapted to my tables:

SELECT JSON_AGG(details.*) FROM (SELECT p.*, JSON_AGG(s.*) AS categories FROM Categories s JOIN Categories p ON s.parentId = p.Id) AS details;

Gave me an incorrect result:

[
  {
    "active": true,
    "categories": [
      // ALL CATEGORIES ARE HERE
   ],
    "id": 110000,
    "name": "Imóveis",
    "parentId": null
  }
]
4

It looks like the documentation could use some more description to go around the example code. Is that what you’re looking for? Or is there something else specific?

5

I honestly could not figure out how to do what I intended. The documented sample is above, and it is not what it is supposed to be.

We’re researching a MSSQL replacement. Hasura was the first choice, but the case/accent sensitive search is a no-go (and not so easy to do).

Single Store is promising, but without an easy way to manipulate JSON (Relational --> JSON --> Relational), is a no go either.

6

JSON_AGG is an aggregate function so it would only return a single row if there is no group by. For your example, you would need to use something like this:

SELECT json_agg(row(id, name, categories) :> record(id int, name text, categories json)))
FROM
(SELECT pc.id, pc.name, json_agg(row(sc.id, sc.name) :> record(id int, name text)) categories
FROM Categories AS pc
JOIN CATEGORIES sc
ON SC.parentID = PC.ID
WHERE pc.parentId IS NULL AND pc.active = 1
GROUP BY pc.id
ORDER BY id)  AS details
7

This worked:

SELECT JSON_AGG(q.*)
FROM
(
    SELECT pc.*, JSON_AGG(sc.*) as children
    FROM Categories AS pc
    INNER JOIN Categories sc ON sc.parentId = pc.Id AND sc.active = 1
    WHERE pc.parentId IS NULL AND pc.active = 1
    GROUP BY pc.id
    ORDER BY id
) AS q

Now I just need to learn how to read a JSON text variable to one or more variables in a stored procedure.

Thank you.

8

and

This might be relevant.

You can just do

SELECT column::string, column::integer, column::iso_date FROM table;