JSON_TABLE equivalent

Help
1

Hello all,
we are running the current version of SingleStore.
I want to return rows from a JSON array.
This is possible in MySQL using JSON_TABLE (https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html). I know that SingleStore does not do this at the moment.
Are there any plans to do this?

In the meantime I was able to get values of the first item from an array using " JSON_EXTRACT_STRING". Does anyone here have an idea how this could be used. Or any other idea for that matter?

Also to give you an example, this is how my array looks like:
[{“NR”:1,“POSITION”:“CEO”,“ID”:“12345678”},{“NR”:2,“POSITION”:“COO”,“ID”:“23456789”}]

I want two rows as the result here.

Thanks,
Ömer

2

Try something like this:

select *
from table(json_to_array('[1,2,3]'));

See SingleStoreDB Cloud · SingleStore Documentation

In your use case, you may wish to use TABLE() to return a set of json objects, then you can use path expressions to promote the json fields to regular fields if that’s what you want.

3

Hi Hanson,
I have actually tried this already, as provided in the example 4 here: SingleStoreDB Cloud · SingleStore Documentation

However, I am receiving the following error: “Unknown data type”, even with your example, or the example provided above. (So not based on our data.)

Any ideas?

Thanks,
Ömer

4

What version of SingleStore are you using exactly? Check the output of

select @@memsql_version;

Also, double check that your quotation marks are straight quotes, not angled quotes, for single and double quotes.

5

I did not use angled quotes, but thanks for pointing this out. As mentioned, I used your example and copied and pasted it from the docs.
The version we are using is 7.3.7
Thanks

6

This is from example 4 in the docs, and it all runs just fine for me on 7.3.7. What are you doing differently?

Test script:

create database db1;
use db1;
create table empRole(Name varchar(20), Role JSON);
insert into empRole values
  ('Adam', '["Team Lead","Security Engineer"]'),
  ('Mark','["Product Manager"]');
SELECT * FROM empRole;
SELECT Name, table_col AS "Title" FROM empRole
JOIN TABLE(JSON_TO_ARRAY(Role));

Output of last 2 queries:

singlestore> SELECT * FROM empRole;
+------+-----------------------------------+
| Name | Role                              |
+------+-----------------------------------+
| Adam | ["Team Lead","Security Engineer"] |
| Mark | ["Product Manager"]               |
+------+-----------------------------------+
2 rows in set (0.04 sec)

singlestore> SELECT Name, table_col AS "Title" FROM empRole
    -> JOIN TABLE(JSON_TO_ARRAY(Role));
+------+---------------------+
| Name | Title               |
+------+---------------------+
| Adam | "Team Lead"         |
| Adam | "Security Engineer" |
| Mark | "Product Manager"   |
+------+---------------------+
3 rows in set (0.04 sec)
7

Hi again,

now it’s getting interesting:
I am using a pre-existing database. I can create the table, I can also insert, but as soon as I select I get the error that I got before: “Unknown data type”
Sounds like a bug to me.
Can we provide anything to you, so you can investigate?

Kind regards,
Ömer

8

If you can post a small repro, that would help.

9

Hi again,

talking to your support I found out that Toad v8.0, which I was using was not supporting that specific feature of SingleStore. Using the Studio your example worked.
However I solved the task in another way in the meantime. So I will not focus on this approach, although I believe it would have also worked. I might try and get back here.

Kind regards,
Ömer

10

Oh, okay. I’m glad you found a solution.