英文:
PostgreSQL jsonb array of objects: how to filter by one of the fields and get the value of the other one
问题
假设我们有两列：id和params。
params是一个jsonb列，其中包含类似以下的对象数组：
[
{
"prop": "a",
"answer": "123"
},
{
"prop": "b",
"answer": "456"
}
]
我需要创建一个查询，返回具有特定值的prop的id + answer（因此每个id最多只有一行）。
例如，您可以按prop=a进行筛选，此时您将仅获得该特定行的123。其他行（具有其他id的行）可以具有不同的值，或者根本没有值（因为它们的json中可能没有prop=a）。
我尝试了一些使用jsonb_array_elements(params)的解决方案，但最终结果集中会有所有可能的值，即使在其中一个元素中只有prop=a（因此每个answer都会与每个重复的id一起重复出现）。
此外，显然不能使用基于数组元素的序数的解决方案。
英文:
Suppose we have two columns: id, params.
params is a jsonb column that contains arrays of objects like:
[
{
"prop": "a",
"answer": "123"
},
{
"prop": "b",
"answer": "456"
}
]
I need to create a query that returns id + answer where prop has some specific value (so at most one row per id).
For example, you can filter by prop=a, and in this case, you'll get 123 for that particular row. Other rows (with other ids) can have a different value or don't have it all (since there could be no prop=a in their jsons).
I tried some solutions with jsonb_array_elements(params) but I always ended up having all possible values from the json even if you have prop=a only in one of the elements (so id is duplicated in the result set with each answer).
Also, I obviously cannot use solutions based on ordinality of the elements in the arrays.
答案1
得分: 1
首先，将表格展开，然后按照Mike Organek的建议进行筛选。the_table CTE是一个模拟真实数据表的示例。
with the_table(id, params) as
(
values
(1, '[{"prop": "a", "answer": "123"},{"prop": "b", "answer": "456"}]'::jsonb),
(2, '[{"prop": "a", "answer": "124"},{"prop": "b", "answer": "457"}]'),
(3, '[{"prop": "a", "answer": "124"},{"prop": "c", "answer": "458"}]')
)
select id, j ->> 'answer' answer
from the_table cross join lateral jsonb_array_elements(params) j
where j ->> 'prop' = 'b';
| id | answer |
|---|---|
| 1 | 456 |
| 2 | 457 |
英文:
First flatten the table and then filter as suggested by Mike Organek. the_table CTE is a mimic of a real data table.
with the_table(id, params) as
(
values
(1, '[{"prop": "a", "answer": "123"},{"prop": "b", "answer": "456"}]'::jsonb),
(2, '[{"prop": "a", "answer": "124"},{"prop": "b", "answer": "457"}]'),
(3, '[{"prop": "a", "answer": "124"},{"prop": "c", "answer": "458"}]')
)
select id, j ->> 'answer' answer
from the_table cross join lateral jsonb_array_elements(params) j
where j ->> 'prop' = 'b';
| id | answer |
|---|---|
| 1 | 456 |
| 2 | 457 |
答案2
得分: 1
以下是翻译好的内容：
根据Mike Organek的建议，您可以使用jsonb_array_elements()函数。
最终查询可以如下所示：
select id,
tmp.parameters->>'prop' AS property,
tmp.parameters->>'answer' AS answer
from data
left join LATERAL jsonb_array_elements(data.params)
WITH ORDINALITY AS tmp (parameters, row_number) ON true
where tmp.parameters->>'prop' = 'a';
Lateral join展开了jsonb对象（顶级元素），您会获得与jsonb列中的'prop'和'answer'属性对应的单独列。
使用where子句以您需要的方式过滤行。
这里是一个演示。
还可以在此答案中找到更多信息。
英文:
As it was suggested by Mike Organek, you can use jsonb_array_elements() function.<br>
The final query can be as follows:
select id,
tmp.parameters->>'prop' AS property,
tmp.parameters->>'answer' AS answer
from data
left join LATERAL jsonb_array_elements(data.params)
WITH ORDINALITY AS tmp (parameters, row_number) ON true
where tmp.parameters->>'prop' = 'a';
Lateral join unwrappes jsonb object (top-level elements) and you get separate columns corresponding for 'prop' and 'answer' properties in a jsonb column.<br>
Use where clause to filter the rows the way you need.
Here is a demo.<br>
Also more can be found in this answer.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论