sqlarraysjsonsinglestore

SingleStore (MemSQL) How to flatten array using JSON_TO_ARRAY

I have a table source:

data
{ "results": { "rows": [ { "title": "A", "count": 61 }, { "title": "B", "count": 9 } ] }}
{ "results": { "rows": [ { "title": "C", "count": 43 } ] }}

And I want a table dest:

title count
A 61
B 9
C 43

I found there is JSON_TO_ARRAY function that might be helpful, but got stuck how to apply it.

How to correctly flatten the json array from the table?

Solution

  • Three options I came up with, which are essentially the same:

    INSERT INTO dest 
WITH t AS(
  SELECT table_col AS arrRows FROM source JOIN TABLE(JSON_TO_ARRAY(data::results::rows))
)
SELECT arrRows::$title as title, arrRows::%count as count FROM t;

    INSERT INTO dest
SELECT arrRows::$title as title, arrRows::%count as count FROM 
(SELECT table_col AS arrRows FROM source JOIN TABLE(JSON_TO_ARRAY(data::results::rows)));

    INSERT INTO dest
SELECT t.table_col::$title as title, t.table_col::%count as count
FROM source JOIN TABLE(json_to_array(data::results::rows)) t;