postgresql 按JSONB内一行中的值排序

yqlxgs2m 于 2023-06-22 发布在 PostgreSQL

关注(0)|答案(1)|浏览(192)

有可能答案是“不要使用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进行排序，除了通过指定3f46c949的local_id将其限制为jsonb中的正确哈希。
如果我使用WHERE ... AND (apps_last_accessed @> '[{"local_id":"3f46c949"}]')，这将缩小结果到登录该应用的位置，但不会缩小我想要按值排序的JSONB哈希值。
我可以用SELECT ... jsonb_array_elements(apps_last_accessed)->>'published' as published将JSONB散列分解到它们自己的行中，这将允许我按published排序，但结果行是apps_last_accessed中的每个散列。我也可以用SELECT ... jsonb_array_elements(apps_last_accessed)->>'local_id' as local_id来划分local_id，但是我不能将AND local_id = '3f46c949'添加到WHERE，因为它不是一个列。
有没有一种方法可以在将jsonb分解成它们自己的行之后子查询我需要的内容？还是完全不同的方法？同样，我知道我可以构建一个具有典型关系的表，其中published是一个普通的datetime列，但我希望避免这种情况。

postgresql

来源：https://stackoverflow.com/questions/76477820/sorting-by-a-value-in-one-row-inside-a-jsonb

1条答案

按热度按时间

vwoqyblh1#

如果我没理解错的话，您希望按照本地id与给定值匹配的json元素的发布日期对表进行排序。
一个选项是在order by子句中使用子查询来解嵌套json数组，过滤其元素并检索相应的日期：

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
)

order by和limit子句在这里，以防有多个行与本地id匹配。
如果你想在resultset中显示该日期，我们可以用横向连接来表达逻辑：

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

赞(0）回复(0）举报 2023-06-22

我来回答

postgresql 按JSONB内一行中的值排序

1条答案

相关问题

热门标签

最新问答