sql - 获取包含 JSONB 值的行，如果值不存在，也获取行

Question

我有一个名为 credentials 的表，其中有一个名为 details 的 jsonb 列和一个名为 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'

这很好用。但是，如果用户:

1. credentials 数组中没有任何对象，或者
2. 没有给定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 不是问题。

Answer 1

使用 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 值的所有行，你应该在 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)    

Try it in rextester.