本文介绍了找不到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更多推荐
找不到jsonb
发布评论