Stack Overflow Asked by Dan Wolchonok on December 20, 2020
I have a jsonb column, progress, on a table (call it t). It has data like this:
{
"2": {
"8": "completed",
"9": "completed",
"10": "completed",
"percent_completed": 0
},
"5": {
"40": "completed",
"percent_completed": 0
}
}
I’m trying to get a table that looks like:
Top Level | Send Level | status
-------------------------------
| 2 | 8 | completed
| 2 | 9 | completed
| 2 | 10 | completed
| 5 | 40 | completed
I am struggling to get a statement that works. I’m almost there (I can get the top level column), but I can’t get the second level. This works to extract the first key:
select top_level
, progress
from t
cross join jsonb_object_keys(progress) top_level
When I then try to get the second level, it doesn’t work. I am struggling to answer why:
select top_level
, second_level
, t
from t
cross join jsonb_object_keys(progress) top_level
cross join jsonb_object_keys(progress->top_level) second_level
I get the following error: ERROR: cannot call jsonb_object_keys on a scalar
I’m using Postgres 11.8
I have tried different json operators and casting the resulting data in a bunch of different ways, but I am struggling to figure it out. Would really appreciate the help.
Use jsonb_each()
and jsonb_each_text()
instead of jsonb_object_keys()
:
select
t1.key as top_level,
t2.key as send_level,
t2.value as value
from t
cross join jsonb_each(progress) as t1
cross join jsonb_each_text(t1.value) as t2
where t2.key <> 'percent_completed'
Read in the documentation about JSON Functions and Operators.
Correct answer by klin on December 20, 2020
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP