基于特定密钥从Amazon RedShift中的jsons数组中提取值 [英] Extract value based on specific key from array of jsons in Amazon Redshift

问题描述

我在RedShift中有一个表名为t1，列名为col1，数据类型为varchar。 但是，数据存储在COL1是json数据中。

col1值： [{"id":1,"value":null},{"id":2,"value":null},{"id":3,"value":"https://www.google.co.in"},{"id":4,"value":"India"}]

现在我想根据键提取数据，即，如果我在WHERE子句中提供id值，它应该获取与该id对应的值

我尝试了以下查询：

select json_extract_array_element_text(col1, 0) as json_value,
json_extract_path_text(json_value, 'value') as value 
from T1

我在上面的查询中找不到基于ID进行筛选的方法。

如果有人能帮我这个忙，我将不胜感激。

推荐答案

您需要将数据与JSON数组交叉连接，该数组具有足够长的整数列表来提取所有数组元素。将行分解后，您可以提取每个行的ID和值，然后应用所需的WHERE子句。

在中有许多这样做的示例，但如果您需要进一步的帮助，请在评论中联系。

=========================================

将json数组和元素分解为数据库行和列的步骤的演示SQL。我对每一步都做了CTE，希望能提高清晰度。还应该注意，对大量原生JSON数据执行查询可能非常慢，建议将经常查询的数据转换为不同的列。此外，所编写的代码将仅展开json数组的10个元素--如果需要更多元素，则数字CTE将需要更多值。

with recursive numbers(n) as  -- this creates the numbers 0 - 9
( select 0 as n
    union all
    select n + 1
    from numbers n
    where n.n < 9
),
inputtext as   -- put your input string into a CTE; you may have a table for this
( select '[{"id":1,"value":null},{"id":2,"value":null},{"id":3,"value":"https://www.google.co.in"},{"id":4,"value":"India"}]'::text as col1
),
exploded as   -- cross join the input data with the numbers to extract all the array elements
( select json_extract_array_element_text(col1, n) as json_value
from inputtext cross join numbers
),
smashed as         -- extract the json elements id and value
( select json_extract_path_text(json_value, 'id') as id, json_extract_path_text(json_value, 'value') as value 
from exploded
where json_value is not null
)
select id, value -- select the id & value of interest
from smashed
where id = 3
;

这篇关于基于特定密钥从Amazon RedShift中的jsons数组中提取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！