主页 / dba / 问题 / 260331
Accepted
lurf jurv
Asked: 2020-02-23 16:22:52 +0800 CST

从布尔列构建数组

  • 772

我有 , , 等列role1。role2它们role3都是布尔值。

我想在这个表上创建一个视图，它的角色列类型为text[]。如果列是TRUE, FALSE, TRUE，则视图将包含["role1", "role3"].

有什么好的方法可以做到这一点不会爆炸成吨CASE WHEN吗？为了澄清，我可以使用 O(n) with CASE WHEN，但不是 O(2^n) ，这是它目前似乎需要的。:)

postgresql array

2 个回答

  1. 您可以将该行转换为 JSON 值，然后使用子选择：

    select t.id, x.roles
from the_table t
  cross join lateral (
     select array_agg(col) as roles
     from jsonb_each_text(to_jsonb(t)) x(col,val)
     where x.val::boolean
       and x.col like 'role%'
  ) x
    • 4
  2. Best Answer

    我建议VALUES在横向子查询中使用表达式。

    SELECT t.id, a.roles
FROM   tbl t
CROSS  JOIN LATERAL (
   SELECT ARRAY(
      SELECT col
      FROM  (
         VALUES
            ('role1', role1) -- eligible columns
          , ('role2', role2)
          , ('role3', role3)
         ) x(col, val)
      WHERE  val
     )
  ) a(roles);

    这将列“反透视”为行，因此我们可以处理一组而不是一行。

    正如a_horse 所展示的，json( b) 的通用性足以涵盖这项任务。在处理所有列时，您无需拼出符合条件的列boolean。但似乎无论如何您都必须拼出符合条件的列。

    细微的差别：这会为“无限定值”( {}) 返回一个空数组，而 a_horse 的查询返回NULL相同的结果。

    由于三个原因，这应该会更快：

    1. 仅处理符合条件的列。特别是与许多其他（可能很大？）列相关。
    2. 涉及更少的来回转换，并且谓词尽可能便宜。
    3. ARRAY 构造函数比array_agg(). 看：

    或者，更简单：用 .CASE连接的普通表达式concat_ws()。如果字符串足够好：

    SELECT t.id
     , concat_ws(','
               , CASE WHEN role1 THEN 'role1' END -- eligible columns
               , CASE WHEN role2 THEN 'role2' END
               , CASE WHEN role3 THEN 'role3' END) AS roles_string
FROM   tbl t;

    应该是最快的解决方案。并没有那么冗长。看：

    或者得到与上面相同的数组：

    SELECT t.id
     , string_to_array(
          concat_ws(
             ','
           , CASE WHEN role1 THEN 'role1' END -- eligible columns
           , CASE WHEN role2 THEN 'role2' END
           , CASE WHEN role3 THEN 'role3' END)
        , ',') AS roles
FROM   tbl t;

    我们还可以从系统目录中动态生成符合条件的列列表。（如果您毕竟不想拼出列。）

    或者缩小到json( b) 技术的合格列，以及早消除可能昂贵的噪音。看：

    db<>fiddle here - 包括。以上所有内容，a_horse 的 jsonb 查询，以及几个变体

    • 4

相关问题