英文:
Sorting by a value in one "row" inside a jsonb
问题
可能的答案是“不要使用jsonb”，但现在我正在尝试避免创建另一个表格。
我有一个表格，用于存储不同应用程序的登录时间。列 apps_last_accessed 的一个示例值如下：
[
{"name": "Atlassian", "local_id": null, "published": "2023-06-06T00:20:16.217Z", "external_id": "asdf"},
{"name": "Bitbucket", "local_id": "3f46c949", "published": "2023-06-06T00:19:58.236Z", "external_id": "fdsa"}
]
当显示登录了一个应用程序的用户列表时，我希望能够按 apps_last_accessed->published 进行排序，但要将其限定为指定 local_id 为 3f46c949 的正确 JSONB 哈希。
如果我使用 WHERE ... AND (apps_last_accessed @> '[{"local_id":"3f46c949"}]')，这将缩小结果范围，仅适用于存在对该应用程序的登录的情况，但不会缩小我想要按值排序的JSONB哈希。
我可以将JSONB哈希拆分成它们自己的行，如下所示：SELECT ... jsonb_array_elements(apps_last_accessed)->>'published' as published，这将允许我按 published 进行排序，但结果行每个哈希都会有一行。我还可以将 local_id 拆分为 SELECT ... jsonb_array_elements(apps_last_accessed)->>'local_id' as local_id，但我不能将 AND local_id = '3f46c949' 添加到 WHERE 子句中，因为它不是一个列。
在将JSONB拆分为它们自己的行后，是否有一种方法可以执行子查询以获取我需要的内容？或者完全不同的方法？再次强调，我知道我可以构建一个具有典型关系的表，其中 published 是一个普通的日期时间列，但我希望避免这样做。
英文:
It's possible the answer to this is "don't use jsonb" but for now I'm trying to avoid creating another table.
I have a table which stores sign in times to various apps. A value for the column apps_last_accessed can look like this:
[
{"name": "Atlassian", "local_id": null, "published": "2023-06-06T00:20:16.217Z", "external_id": "asdf"},
{"name": "Bitbucket", "local_id": "3f46c949", "published": "2023-06-06T00:19:58.236Z", "external_id": "fdsa"}
]
When displaying a list of users who have signed into one app, I would like to be able to sort by apps_last_accessed->published, except scope it to the correct hash in the jsonb by specifying the local_id of 3f46c949.
If I use WHERE ... AND (apps_last_accessed @> '[{"local_id":"3f46c949"}]'), that will narrow results to where a sign in to that app exists, but does nothing to narrow the JSONB hash that I want to sort by a value of.
I can break out the JSONB hashes into their own row with SELECT ... jsonb_array_elements(apps_last_accessed)->>'published' as published and that will let me sort by published, but the result rows are one for each hash in apps_last_accessed. I could also break out the local_id with SELECT ... jsonb_array_elements(apps_last_accessed)->>'local_id' as local_id, but I can't then add AND local_id = '3f46c949' to the WHERE because it's not a column.
Is there a way to sub-query what I need after breaking out the jsonb into their own rows? Or a different approach entirely? Again, I'm aware I can build out a table with typical relations where published is a normal datetime column but I'm looking to avoid that.
答案1
得分: 2
If I follow you correctly, you want to sort your table by the publish date of the json element whose local id matches a given value.
One option uses a subquery in the order by clause to unnest the json array, filter its elements, and retrieve the corresponding date:
select *
from mytable t
order by (
select j ->> 'published'
from jsonb_array_elements(apps_last_accessed) as x(j)
where j ->> 'local_id' = '3f46c949'
order by 1 desc limit 1
)
The order by and limit clauses are here in case there is more than one row that matches the local id.
If you want to display that date in the resultset, we can phrase the logic with a lateral join instead:
select t.*, x.*
from mytable t
left join lateral (
select j ->> 'published'
from jsonb_array_elements(apps_last_accessed) as x(j)
where j ->> 'local_id' = '3f46c949'
order by 1 desc limit 1
) x(last_published) on true
order by x.last_published
英文:
If I follow you correctly, you want to sort your table by the publish date of the json element whose local id matches a given value.
One option uses a subquery in the order by clause to unnest the json array, filter its elements and retrieve the corresponding date:
select *
from mytable t
order by (
select j ->> 'published'
from jsonb_array_elements(apps_last_accessed) as x(j)
where j ->> 'local_id' = '3f46c949'
order by 1 desc limit 1
)
The order by and limit clauses are here in case there is more than one row that matches the local id.
If you want to display that date in the resultset, we can phrase the logic with a lateral join instead:
select t.*, x.*
from mytable t
left join lateral (
select j ->> 'published'
from jsonb_array_elements(apps_last_accessed) as x(j)
where j ->> 'local_id' = '3f46c949'
order by 1 desc limit 1
) x(last_published) on true
order by x.last_published
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论