Get all the values from nested JSON arrays in PostgreSQL

I want to extract all the app_indexe in abc array using the raw query. The database I’m using PostgreSQL 10.9. I’m successfully able to extract till abc key. what I’m able to get is by passing the index number of the array. but I want all the app_index.

without index:

select v_info->'abc' from table1

with index: select v_info->'abc'->>0 from table1

{
        "id": 1406711300166,
        "abc": [ 
            {
                "am": "1.74",
                "am_set": {
                    "sh_mon": {
                        "am": "1.74",
                        "cur_code": "ABC"
                    },
                    "pre_money": {
                        "amount": "1.74",
                        "code": "ABC"
                    }
                },
                "app_index": 0
            },
           {
                "am": "1.74",
                "am_set": {
                    "sh_mon": {
                        "am": "1.74",
                        "cur_code": "ABC"
                    },
                    "pre_money": {
                        "amount": "1.74",
                        "code": "ABC"
                    }
                },
                "app_index": 1
            }
        ],
        "xyx": 0,
        "zyx": "random var"
    }

what I need is all the values in the app_index of abc.

output

abc_index
----------
0
1

Answer

You can unnest the array with json(b)_array_elements() and then just access the value of attribute app_index:

select el -> 'app_index' abc_index
from mytable t
cross join lateral jsonb_array_elements(t.v_info -> 'abc') a(el)

Demo on DB Fiddle:

| abc_index |
| :-------- |
| 0         |
| 1         |