找不到jsonb

本文介绍了找不到jsonb_array_elements时不返回行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我使用交叉联接对嵌套的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