时间戳

首页 > 解决方案 > 如何按主键 ID 的 JSONB 数组指定的顺序选择行？

arrays - 如何按主键 ID 的 JSONB 数组指定的顺序选择行？

问题描述

这是我的虚拟设置：

CREATE TABLE containers (
    id SERIAL PRIMARY KEY,
    positions jsonb
);
CREATE TABLE bits (
    id SERIAL PRIMARY KEY,
    container_id integer REFERENCES containers(id) ON DELETE CASCADE ON UPDATE CASCADE,
    data jsonb
);

中的示例行containers：

id  positions
1   [4, 2, 3]

我想要完成的是使用positionsincontainers来指示返回位的顺序。这似乎比使用具有 0、1、2、3 等值的smallint position列更容易，并且在用户重新排序位时必须全部更新。bits

本质上，我想要做的是使用positions数组ORDER BY，例如（伪代码）：

SELECT b.id, b.data FROM bits b, container c WHERE b.container_id = 1 ORDER BY (jsonb_array_elements(c.positions));

所需的输出将是：

id  data
4   {"banner": "This is a message!"}
2   {"name": "Bob"}
3   {"playlistId": 3}

我该如何做到这一点？我正在使用 Postgres 10.7。

标签： arraysjsonpostgresqlsql-order-bypostgresql-10

解决方案

您需要使用jsonb函数来执行此操作。

请尝试这样的事情：

select b.*
  from bits b
       join containers c
         on c.id = b.container_id
       join lateral jsonb_array_elements_text(c.positions) 
                      with ordinality as p(id, rn)
         on p.id::int = b.id
 where b.container_id = 1
 order by p.rn::int

推荐阅读