0

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.

klin's user avatar
klin
123k15 gold badges240 silver badges262 bronze badges
askedSep 4, 2020 at 1:47
Dan Wolchonok's user avatar
2
  • I don't think you can reference the top_level in the second join because the query engine doesn't know what you mean.CommentedSep 4, 2020 at 1:53
  • Looking atpostgresql.org/docs/9.5/functions-json.html it looks like you might try#> as it gets the JSON Object and not the JSON Object Field.CommentedSep 4, 2020 at 2:00

1 Answer1

1

Usejsonb_each() andjsonb_each_text() instead ofjsonb_object_keys():

select     t1.key as top_level,     t2.key as send_level,    t2.value as valuefrom tcross join jsonb_each(progress) as t1cross join jsonb_each_text(t1.value) as t2where t2.key <> 'percent_completed'

Db<>fiddle.

Read in the documentation aboutJSON Functions and Operators.

answeredSep 4, 2020 at 2:54
klin's user avatar
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you so much for the help klin!

Your Answer

Sign up orlog in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

By clicking “Post Your Answer”, you agree to ourterms of service and acknowledge you have read ourprivacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.