找不到jsonb_array_elements时不返回行 [英] jsonb_array_elements does not return row, when not found

问题描述

我使用交叉联接对嵌套的json值进行嵌套，但是当未归档时，它不返回任何记录.下面使用左连接是否有解决方案?

I use cross join to unnest nested json values, but when filed not found it returns no record. is there a solution for the below using left join somehow?

create table json_example as
select '{"id": "21397",  "fields": { "labels": [] , "subtasks": [{"id": "10217"}] }}'::jsonb as value
union all
select '{"id": "21397",  "fields": { "labels": [] ,  "subtasks" : []  }}'::jsonb

返回两行:

select * from json_example

以下仅返回一行:

select subtasks.value->>'id'  
  from json_example h cross join
       jsonb_array_elements(value->'fields'->'subtasks') as subtasks

我该如何解决上述两个记录的问题?

how i can solve the above, problem to have both records?

推荐答案

您可以添加on true来使left join正常工作.

You may add on true for left join to work.

select subtasks->>'id'  
  from json_example h left join
       jsonb_array_elements(value->'fields'->'subtasks') as subtasks on true

这篇关于找不到jsonb_array_elements时不返回行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！