sql - 如何在 PostgreSQL 中查询 JSON 列以获取唯一对象值

Question

我正在查询表以获取给定 JSON 列中的不同值列表。

在下面的代码片段中，Survey_Results 表有 3 列:姓名、电子邮件和有效负载。 Payload是我要查询的JSON对象。

Table Name: Survey_Results

Name         Email                      Payload

Ying         SmartStuff@gmail.com     [
                                      {"fieldName":"Product Name", "Value":"Calculator"},
                                      {"fieldName":"Product Price", "Value":"$54.99"}
                                      ]

Kendrick     MrTexas@gmail.com        [
                                      {"fieldName":"Food Name", "Value":"Texas Toast"},
                                      {"fieldName":"Food Taste", "Value":"Delicious"}
                                      ]

Andy         WhereTheBass@gmail.com   [
                                      {"fieldName":"Band Name", "Value":"MetalHeads"}
                                      {"fieldName":"Valid Member", "Value":"TRUE"}
                                      ]

我正在寻找所有提及的字段名称的唯一列表。

理想的答案是查询给我一个包含“产品名称”、“产品价格”、“食品名称”、“食品口味”、“乐队名称”和“有效成员”的列表。

在 Postgres 中有这样的可能吗？

Answer 1

使用jsonb_array_elements()在横向连接中:

select distinct value->>'fieldName' as field_name
from survey_results
cross join json_array_elements(payload)

  field_name   
---------------
 Product Name
 Valid Member
 Food Taste
 Product Price
 Food Name
 Band Name
(6 rows)    

How to find distinct Food Name values?

select distinct value->>'Value' as food_name
from survey_results
cross join json_array_elements(payload)
where value->>'fieldName' = 'Food Name'

  food_name  
-------------
 Texas Toast
(1 row)

Db<>fiddle.

重要。请注意，json 结构不合逻辑，因此不必要地庞大和复杂。而不是

[
    {"fieldName":"Product Name", "Value":"Calculator"},
    {"fieldName":"Product Price", "Value":"$54.99"}
]

使用

{"Product Name": "Calculator", "Product Price": "$54.99"}

打开这个db<>fiddle看到正确的 json 结构意味着更简单和更快的查询。