Get all the values from nested JSON arrays in PostgreSQL

Question

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

Please show us the result that you expect, as tabular text.

GMB
– GMB

2020-03-29 16:51:11 +00:00
Commented Mar 29, 2020 at 16:51 — GMB
– GMB, Commented Mar 29, 2020 at 16:51
@GMB I have edited my question. you can get a rough idea.

wizard
– wizard

2020-03-29 16:57:50 +00:00
Commented Mar 29, 2020 at 16:57 — wizard
– wizard, Commented Mar 29, 2020 at 16:57
What version of postgres are you on

Haleemur Ali
– Haleemur Ali

2020-03-29 16:58:26 +00:00
Commented Mar 29, 2020 at 16:58 — Haleemur Ali
– Haleemur Ali, Commented Mar 29, 2020 at 16:58
@HaleemurAli PostgreSQL 10.9

wizard
– wizard

2020-03-29 17:02:21 +00:00
Commented Mar 29, 2020 at 17:02 — wizard
– wizard, Commented Mar 29, 2020 at 17:02

GMB · Accepted Answer · 2020-03-29 17:02:40Z

1

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         |

answered Mar 29, 2020 at 17:02

GMB

224k25 gold badges102 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Get all the values from nested JSON arrays in PostgreSQL

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related