如何从PostgreSQL中的json数组中获取具有唯一编号的元素？

Question

我有一张银行账户表：

    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 |              | 

它在jsonb列中有一些JSON：

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

我使用jsonb\u array\u元素来获取一个用户的帐户列表：

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

太好了。但我如何让每一行都有一个唯一的id呢？我想将每一行映射到一个hibernate对象，但我很难做到这一点，因为我找不到让每一行都具有唯一id的方法。

Answer 1

使用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;

如果您不喜欢在银行账户中有空值或空值的行，请使用更简单的交叉联接：

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

关键成分是with ORDINALITY，用于动态生成设置返回函数的行号。它是在Postgres 9.4中引入的——就像jsonb一样。

rn在银行账户中每个基础行都是唯一的
要在整个结果集中保持唯一性，请将其与b.id结合使用。

关于带序数的：

• 带有元素号的PostgreSQL unnest（）

相关：

• 查询JSON类型内的数组元素
• 如何在PostgreSQL 9.4中将简单的json（b）int数组转换为整数[]