Expand nested arrays postgres

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.

One Answer

Use jsonb_each() and jsonb_each_text() instead of jsonb_object_keys():

    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

Add your own answers!

Related Questions

Find the best matching combination of numbers

0  Asked on December 18, 2020 by stkvtflw


toUpperCaseFunction does not work with await keyword

2  Asked on December 18, 2020 by goxarad784


How to split given linked list

1  Asked on December 18, 2020 by evgeni-nabokov


delete bigquery table from spark by using scala

1  Asked on December 17, 2020 by counter10000


Python : find_all() return an empty list

2  Asked on December 17, 2020 by bastien


expected false to deeply equal true on postman

2  Asked on December 17, 2020 by cristian-hernandez


Split the data in the specific column in the DataFrame

2  Asked on December 17, 2020 by jaehyeon-robert-han


preventDefault not working when form submit react js

2  Asked on December 17, 2020 by gihankumara


Ask a Question

Get help from others!

© 2022 All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP