sql - 获取具有 JSONB 值的行，以及如果值不存在的行

问题描述

我有一个名为的表credentials，其中有一个jsonb名为的列details和一个名为user_id. 该details列有一个名为的数组systems，它可以是空的，也可以包含如下所示的对象：{ system_id: TXT, system_value: TXT }

我有以下查询，它假装为每个用户获取给定 system_id 的所有 system_values：

SELECT credentials.user_id, systems->>'system_value' AS system_value
FROM  credentials, jsonb_array_elements(credentials.details->'systems') systems
WHERE systems->>'system_id' = 'a-given-id'

这工作正常。但是，如果用户：

credentials数组内没有任何对象或
没有给定的对象system_id

它不会为他们返回一行。我想返回一行system_value是NULL.

因此，例如，对于给定的数据：

user_id, credentials
1, { "systems": [{ "system_id": "x", "system_value": "success" }] }
2, { "systems": [{ "system_id": "y", "system_value": "failure" }] }
3, { "systems": [] }

我希望在查询后得到：

user_id, system_value
1, "success"
2, NULL
3, NULL

但是根据我当前的查询，我得到了：

user_id, system_value
1, "success"

我应该如何修改查询以考虑那些在“系统”上没有值或给定“system_id”没有值的凭据？

请注意，即使我这样做：

SELECT credentials.user_id, systems->>'system_value' AS system_value
FROM  credentials, jsonb_array_elements(credentials.details->'systems') systems

它不会返回根本没有credentails.details任何元素的用户。所以我认为这不是where问题所在。

标签： sqlpostgresqljsonb

用于LEFT JOIN ... ON true获取所有行：

SELECT credentials.user_id, systems->>'system_value' AS system_value
FROM  credentials
LEFT JOIN jsonb_array_elements(credentials.details->'systems') systems ON true

 user_id | system_value 
---------+--------------
       1 | success
       2 | failure
       3 | 
(3 rows)

如果您想获取具有给定值的所有行，system_id您应该使用条件表达式 in SELECT，作为WHERE仅过滤行的条件：

SELECT 
    c.user_id, 
    CASE systems->>'system_id'
        WHEN 'x' THEN systems->>'system_value' 
        ELSE null
    END AS system_value
FROM  credentials c
LEFT JOIN jsonb_array_elements(c.details->'systems') systems ON true

 user_id | system_value 
---------+--------------
       1 | success
       2 | 
       3 | 
(3 rows)

在 rextester 中尝试。

sql - 获取具有 JSONB 值的行，以及如果值不存在的行

问题描述

解决方案

推荐阅读