如何在Presto中提取嵌套json数组对象中的键

Question

我正在使用最新的(0.117)Presto，并试图执行CROSS JOIN UNNEST与复杂的JSON数组，像这样。

[{"id": 1, "value":"xxx"}, {"id":2, "value":"yy"}, ...]

为了做到这一点，首先我尝试用id by 的值创建一个ARRAY

SELECT CAST(JSON_EXTRACT('[{"id": 1, "value":"xxx"}, {"id":2, "value":"yy"}]', '$..id') AS ARRAY<BIGINT>)

但是它不工作。

提取id值的最佳JSON路径是什么?

Answer 1

这将解决您的问题。它更通用地转换为json的ARRAY(在给定任意映射结构的情况下更不容易出错):

select 
        TRANSFORM(CAST(JSON_PARSE(arr1) AS ARRAY<JSON>), 
                   x -> JSON_EXTRACT_SCALAR(x, '$.id'))
from 
       (values ('[{"id": 1, "value":"xxx"}, {"id":2, "value":"yy"}]')) t(arr1)

输出:

 [1,2]

…我遇到了一个json列表嵌套在json中的情况。我的json列表有一个模棱两可的嵌套映射结构。以下代码返回json列表中给定特定键的值数组。

1. 使用JSON提取列表
2. 将列表转换为json数组
3. 使用TRANSFORM函数遍历数组中的json元素，并提取您感兴趣的键的值。

>

TRANSFORM(CAST(JSON_EXTRACT(json, '$.path.toListOfJSONs') AS ARRAY<JSON>),
          x -> JSON_EXTRACT_SCALAR(x, '$.id')) as id

Answer 2

您可以将JSON转换为MAP的ARRAY，并使用transform lambda函数提取"id"键:

select 
        TRANSFORM(CAST(JSON_PARSE(arr1) AS ARRAY<MAP<VARCHAR, VARCHAR>>), entry->entry['id'])
from 
       (values ('[{"id": 1, "value":"xxx"}, {"id":2, "value":"yy"}]')) t(arr1)

输出:

 [1, 2]

Answer 3

现在，您可以使用presto-third-functions，它提供json_array_extract函数，您可以提取json数组信息，如:

    select 
           json_array_extract_scalar(arr1, '$.book.id') 
    from 
           (values ('[{"book":{"id":"12"}}, {"book":{"id":"14"}}]')) t(arr1)

输出是:

    [12, 14]

Answer 4

我最终放弃了寻找一个简单的JSON路径来提取它们。

相反，我编写了如下所示的冗余脏查询来完成任务。

SELECT
  ...
FROM
  (
    SELECT
      SLICE(ARRAY[
        JSON_EXTRACT(json_column, '$[0].id'),
        JSON_EXTRACT(json_column, '$[1].id'),
        JSON_EXTRACT(json_column, '$[2].id'),
        ...
      ], JSON_ARRAY_LENGTH(json_column)) ids
    FROM
     the.table
  ) t1
  CROSS JOIN UNNEST(ids) AS t2(id)
WHERE
  ...

我仍然想知道最佳实践，如果你知道另一种好方法来交叉连接他们!