How to get elements with a unique number from a json array in PostgreSQL?

Question

I have a table bank_accounts:

    Column     |         Type          |                                Modifiers                                | Storage  | Stats target | Description 
---------------+-----------------------+-------------------------------------------------------------------------+----------+--------------+-------------
 id            | integer               | not null default nextval('bank_accounts_id_seq'::regclass)              | plain    |              | 
 name          | character varying(50) |                                                                         | extended |              | 
 bank_accounts | jsonb                 | not null                                                                | extended |              | 

And it has some JSON in the jsonb column:

 id | name  |                              bank_accounts                               
----+-------+--------------------------------------------------------------------------
  1 | test1 | [{"name": "acct1", "balance": -500}, {"name": "acct2", "balance": -300}]

And I am using jsonb_array_elements to get a list of the accounts for one user:

select jsonb_array_elements(bank_accounts)->>'name' as name, jsonb_array_elements(bank_accounts)->>'balance' as balance from bank_accounts;
 name  | balance 
-------+---------
 acct1 | -500
 acct2 | -300

That's all great. But how do I get each row to have a unique id? I'd like to map each row to a hibernate object, but I'm having trouble doing that because I can't find a way to get each row to have a unique id.

Answer 1

Try a different, clean approach with JOIN LATERAL:

SELECT b.id, t.rn
     , t.account->>'name' AS name
     , t.account->>'balance' AS balance
FROM   bank_accounts b
LEFT   JOIN LATERAL jsonb_array_elements(b.bank_accounts)
                    WITH ORDINALITY AS t (account, rn) ON true;

If you don't care for rows with empty or null values in bank_accounts, use a simpler CROSS JOIN:

SELECT ...
FROM   bank_accounts b
     , jsonb_array_elements(b.bank_accounts) WITH ORDINALITY AS t (account, rn);

The key ingredient is WITH ORDINALITY to produce row numbers for set-returning functions on the fly. It was introduced with Postgres 9.4 - just like jsonb.

rn is unique per underlying row in bank_accounts.
To be unique across the whole result set, combine it with b.id.

About WITH ORDINALITY:

• PostgreSQL unnest() with element number

Related:

• Query for array elements inside JSON type
• How to turn a simple json(b) int array into an integer[] in PostgreSQL 9.4+