Array json split elements to colums postgres

Question

I have this column extra which is JSONB from table called subscribers and the value for a given subscriber is :

{
  "valid": "N",
  "msisdn": "23490272",
  "account_info": [
    {
      "account_id": 110000616,
      "account_cur": "NGN",
      "account_type": "C",
      "account_class": "ww"
    },
    {
      "account_id": 110000617,
      "account_cur": "NGN",
      "account_type": "S",
      "account_class": "GHCXLA"
    },
    {
      "account_id": 110000618,
      "account_cur": "EUR",
      "account_type": "S",
      "account_class": "ww"
    },
    {
      "account_id": 110000619,
      "account_cur": "USD",
      "account_type": "C",
      "account_class": "ww"
    },
    {
      "account_id": 110000620,
      "account_cur": "SAR",
      "account_type": "Y",
      "account_class": "ww"
    },
    {
      "account_id": 110000621,
      "account_cur": "NGN",
      "account_type": "Y",
      "account_class": "ww"
    },
    {
      "account_id": 110000622,
      "account_cur": "NGN",
      "account_type": "S",
      "account_class": "ww"
    },
    {
      "account_id": 110000623,
      "account_cur": "NGN",
      "account_type": "S",
      "account_class": "ww"
    },
    {
      "account_id": 110000624,
      "account_cur": "NGN",
      "account_type": "S",
      "account_class": "ww"
    },
    {
      "account_id": 110000625,
      "account_cur": "NGN",
      "account_type": "S",
      "account_class": "ww"
    },
    {
      "account_id": 110000626,
      "account_cur": "NGN",
      "account_type": "S",
      "account_class": "ww"
    },
    {
      "account_id": 110000627,
      "account_cur": "NGN",
      "account_type": "S",
      "account_class": "ww"
    },
    {
      "account_id": 110000628,
      "account_cur": "NGN",
      "account_type": "S",
      "account_class": "ww"
    },
    {
      "account_id": 110000629,
      "account_cur": "NGN",
      "account_type": "S",
      "account_class": "ww"
    },
    {
      "account_id": 110000630,
      "account_cur": "NGN",
      "account_type": "S",
      "account_class": "ww"
    }
  ],
  "preapprovalId": "1517299109619",
  "reservedAmount": 0.0,
  "preapprovalStatus": "APPROVED"
}

I want to make the elements from the account_info into columns.I have tried this :

select extra #>'{account_info,0}'->>'account_id' Account,extra #>'{account_info,0}'->>'account_cur' Currency,extra #>'{account_info,0}'->>'account_type' Account_Type from subscribers s2 where id = 319;
  account  | currency | account_type
-----------+----------+--------------
 110000616 | NGN      | C

How can i have all the elements?

Do you want each array element as a row in your output? Please edit your question (by clicking on the edit link below it) and add the expected output based on your sample data as formatted text. — user330315
– user330315, Commented Jul 10, 2020 at 14:17

GMB · Accepted Answer · 2020-07-10 14:20:35Z

2

If I followed you correctly, you can use jsonb_array_elements() and a lateral join to unnest each array element to a separate row:

select 
    x.acc ->> 'account_id' account,
    x.acc ->> 'account_cur' currency,
    x.acc ->> 'account_type' account_type
from subscribers s
cross join lateral jsonb_array_elements(s.extra -> 'account_info') as x(acc)
where s.id = 319

answered Jul 10, 2020 at 14:20

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

Labros Karantzas Over a year ago

GMB thank you that solved my problem. :) !!!!! I wasn't that familiar with lateral joins .

Collectives™ on Stack Overflow

Array json split elements to colums postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related