如何在SQL中结合JSON进行交叉连接并获取全部数据？

需要查询获取tbl_jsontesting表中的所有记录（包括与tbl_registred表无匹配的记录），但当前执行的SQL仅返回两表匹配的记录，需调整查询语句。

tbl_jsontesting表 #

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| id | data                                                                                                                                                                               | description |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1  | {"complexProperties":[{"properties":{"key":"Registred","Value":"123456789"}},{"properties":{"key":"Urgency","Value":"Total"}},{"properties":{"key":"ImpactScope","Value":"All"}}]} | Some Text   |
| 2  | {"complexProperties":[{"properties":{"key":"Registred","Value":"123456788"}},{"properties":{"key":"Urgency","Value":"Total"}},{"properties":{"key":"ImpactScope","Value":"All"}}]} | Some Text2  |
| 3  | {"complexProperties":[{"properties":{"key":"Urgency","Value":"Total"}},{"properties":{"key":"ImpactScope","Value":"All"}}]}                                                        | Some Text3  |
| 4  | {}                                                                                                                                                                                 | Some Text4  |
| 5  | {"complexProperties":[]}                                                                                                                                                           | Some Text5  |
| 6  |                                                                                                                                                                                    | Some Text6  |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

tbl_registred表 #

----------------------
| id        | name   |
----------------------
| 123456789 | Source |
| 123456788 | Cars   |
----------------------

当前查询语句 #

select jt.id, rg.id as id_registred, rg.name, jt.description
from tbl_jsontesting jt
  cross join jsonb_array_elements(jt.data::jsonb -> 'complexProperties') as p(props)
   join tbl_registred rg 
     on rg.id::text = (p.props -> 'properties' ->> 'Value')
    and p.props -> 'properties' ->> 'key' = 'Registred'
;

当前查询结果 #

--------------------------------------------
| id | id_registred | name   | description |
--------------------------------------------
| 2  | 123456788    | Cars   | Some Text2  |
| 1  | 123456789    | Source | Some Text   |
--------------------------------------------

期望查询结果 #

--------------------------------------------
| id | id_registred | name   | description |
--------------------------------------------
| 6  |              |        | Some Text6  |
| 5  |              |        | Some Text5  |
| 4  |              |        | Some Text4  |
| 3  |              |        | Some Text3  |
| 2  | 123456788    | Cars   | Some Text2  |
| 1  | 123456789    | Source | Some Text   |
--------------------------------------------

解决方案 #

调整查询语句，通过left join lateral和left join保留主表所有记录，同时处理JSON字段为空的情况：

select 
  jt.id, 
  rg.id as id_registred, 
  rg.name, 
  jt.description
from tbl_jsontesting jt
left join lateral (
  select (props -> 'properties' ->> 'Value') as registred_value
  from jsonb_array_elements(coalesce(jt.data::jsonb -> 'complexProperties', '[]'::jsonb)) as props
  where props -> 'properties' ->> 'key' = 'Registred'
) as p on true
left join tbl_registred rg on rg.id::text = p.registred_value
order by jt.id desc;

关键调整说明 #

• 使用coalesce(jt.data::jsonb -> 'complexProperties', '[]'::jsonb)处理data字段为空、complexProperties不存在或为空数组的场景，避免子查询无结果导致主表记录丢失；
• left join lateral确保主表每条记录都能被保留，即便子查询没有匹配的Registred属性；
• 最后通过order by jt.id desc让结果顺序与期望一致。

内容的提问来源于stack exchange，提问作者executable