如何解析Postgresql中的JSON数组？ [英] How can I parse JSON arrays in postgresql?

问题描述

我正在使用PostgreSQL 9.5.14，并且在表中有一列，其中包含我需要解析其内容的JSON数组。

I am using PostgreSQL 9.5.14, and have a column in a table that contains JSON arrays that I need to parse for their contents.

通过选择，我可以看到JSON的结构是这样的：

Using a select I can see that the structure of the JSON is of this kind:

SELECT rule_results from table limit 5; 

结果：

[{"rule_key":"applicant_not_lived_outside_eu"},{"rule_key":"family_assets_exceed_limit"},{"rule_key":"owned_a_deed"}]

[]

[]

[{"rule_key":"family_category","details":"apply_with_parents_under_25"}]

[]

我无法创建SQL命令来提供 rule_key 键的值。

I have been unable to create an SQL command to give me the values of the rule_key keys.

我试图使用postgresql中json函数的文档从
中找到解决方案 https://www.postgresql.org/docs/9.5/functions-json.html

I've attempted to use the documentation for json-functions in postgresql to find a solution from https://www.postgresql.org/docs/9.5/functions-json.html

SELECT rule_results::json->'rule_key' as results from table;

这仅给我空值。

SELECT jsonb_object_keys(rule_results::jsonb) from table;

这将导致错误消息msg 无法在标量上调用jsonb_object_keys ，这似乎意味着查询仅限于一行。

This results in the error msg "cannot call jsonb_object_keys on a scalar", which seems to mean that the query is limited to a single row.

这看起来很简单，一个包含key：value对的数组，但是答案不知所措。我将不胜感激。

This looks simple enough, an array with key:value pairs, but somehow the answer eludes me. I would appreciate any help.

推荐答案

演示：db 小提琴

可能有不同的解决方案。最终取决于您的期望。但是所有解决方案都将使用函数json_array_elements（）。这会将每个元素扩展为一行。这样，您就可以做任何您想做的事情。

Different solutions are possible. It depends on what you are expecting finally. But all solutions would use the function json_array_elements(). This expands every element into one row. With that you can do whatever you want.

这将导致每个值一行：

SELECT 
    value -> 'rule_key'
FROM
    data,
    json_array_elements(rule_results)

这篇关于如何解析Postgresql中的JSON数组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！