SingleStore(MemSQL)如何使用JSON_to_array展平数组

Question

我有一个表source:

数据
{"结果"：{"行"：[{"标题"："A"，"计数"：61}
{"结果"：{"行"：[{"标题"："C"，"计数"：43}}}}

以下内容适用于您的示例，但它可能会对您的语法有所帮助。

在这个查询中，我创建了一个名为json_tab的表，其中有一列名为jsondata。

With t as (
select table_col AS title FROM json_tab join TABLE(JSON_TO_ARRAY(jsondata::results::rows)))
SELECT t.title::$title title,t.title::$count count FROM t

我从代码片段中取了一个例子来处理JSON列中的嵌套数组

https://github.com/singlestore-labs/singlestoredb-samples/blob/main/JSON/Analyzing_nested_arrays.sql

我提出的三个选项基本相同：

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;

它给出了您所请求的确切输出。

with t(rows_) as (
SELECT a.*
FROM TABLE(['{"results": { "rows": [ { "title": "A", "count": 61 }, { "title": "B", "count": 9 } ] }}','{ "results": { "rows": [ { "title": "C", "count": 43 } ] }}']) a
),t1 as (
select table_col AS tname
from t 
JOIN TABLE(JSON_TO_ARRAY(t.rows_::results::$rows)) a
)
SELECT t1.tname::$title as title ,t1.tname::%count as count from t1

有用链接：

Singstore TABLE

使用JSON 的Singlestore

单个存储JSON函数

单存储JSON运算符