arrays - 如何遍历 sqlite 中嵌套的 json 对象中的数组？

Question

假设我有一个 sqlite 表 features，其中有一列 data 包含 json 对象。

CREATE TABLE 特性(id INTEGER PRIMARY KEY, data json)

现在，一个示例数据对象可能是:

{"A":
   {"B":
      {"coordinates":[
         {"x":1, "y":10},
         {"x":10, "y":2},
         {"x":12, "y":12}
       ]
    }
}

现在 coordinates 数组中的 json 对象的数量可以因行而异。一些文档可以有 3 个坐标(上面的示例)，而其他文档可能有 5 个或更多坐标。

对于每一行或文档，我希望能够仅遍历 x 值并找到最小值，对于 y 值也是如此。因此，该示例的结果对于 x 为 1，对于 y 为 2。

我可以使用 json_each 获取数组中的所有 json 对象，但我无法仅提取一行的 x。我试过:

select value from features, json_each(json_extract(features.data, '$.A.B.coordinates'));

但是，这似乎为所有行返回“所有”坐标 json 对象。如何为一个文档迭代数组并从中提取值，以便我可以为一个文档选择最小值或最大值？

Answer 1

在 json_each(json_extract()) 之后再次使用 json_extract() 来提取每个 x 和 y 并且聚合:

SELECT f.id, 
       MIN(json_extract(value, '$.x')) x, 
       MIN(json_extract(value, '$.y')) y
FROM features f, json_each(json_extract(f.data, '$.A.B.coordinates'))
GROUP BY f.id

参见 demo .