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

本文介绍了如何从PostgreSQL的json数组中获取具有唯一编号的元素?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一张桌子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 | |

在jsonb列中有一些JSON:

And it has some JSON in the jsonb column:

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

我正在使用jsonb_array_elements来获取一个用户的帐户列表:

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

那太好了.但是，如何使每一行具有唯一的ID?我想将每一行映射到一个休眠对象，但是这样做很麻烦，因为我找不到一种使每一行具有唯一ID的方法.

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.

推荐答案

尝试使用JOIN LATERAL的另一种干净方法:

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;

如果您不关心bank_accounts中具有空值或空值的行，请使用更简单的CROSS JOIN:

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

select b.id, t.rn , t.account->>'name' AS name , t.account->>'balance' AS balance FROM bank_accounts b , jsonb_array_elements(b.bank_accounts) WITH ORDINALITY AS t (account, rn);

问题的关键要素是WITH ORDINALITY，它会为设置返回功能即时生成行号.它是在Postgres 9.4中引入的-适用于您，jsonb在9.4中也引入了.

The key element for your problem is WITH ORDINALITY which produces row numbers on the fly for set-returning functions. It was introduced with Postgres 9.4 - works for you, jsonb was also introduced with 9.4.

这些内容在每个基础行中都是唯一的.为了在整个表中保持唯一，请添加基础表的id.

Those are unique per underlying row. To be unique across the whole table, add the id of the underlying table.

WITH ORDINALITY的详细信息:

• 带有元素编号的PostgreSQL unnest()

相关:

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

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