代码之家 › 专栏 › 技术社区 › Alexandre Sauvé

在jsonb Postgresql字段的特定索引/位置添加数组

jsonb sorting postgresql arrays

Alexandre Sauvé · 技术社区 · 6 年前

我想知道在Postgresql 9.6中是否有方法将数组附加到jsonb数组中的特定索引/位置?

假设下面的代码是我的json:

{
  "date": "2018-02-12",
  "author": "devoplex",
  "block": [
    { "color": "#C70039", "title": "Fruit" },
    { "color": "#DAF7A6", "title": "Vegetable" },
    { "color": "#DAF7A6", "title": "Meat" }
  ]
}

我想将此对象附加到我的“块”数组中:

{ "color": "#581845", "title": "Candy" }

但我希望这个对象成为第三个索引/位置,而不替换任何内容。所以最后我可以得到这个结果:

{
  "date": "2018-02-12",
  "author": "devoplex",
  "block": [
    { "color": "#C70039", "title": "Fruit" },      <---- Initial line
    { "color": "#DAF7A6", "title": "Vegetable" },  <---- Initial line
    { "color": "#581845", "title": "Candy" },      <---- New line
    { "color": "#DAF7A6", "title": "Meat" }        <---- Initial line
  ]
}

这个例子不是我的实际代码,但它是相同的问题。这是为了构造一个表单,所以它需要按照特定的顺序,否则就没有任何意义。谢谢你。

1 回复 | 直到 6 年前

Vao Tsun 6 年前

丑陋的车轮,但没有比它更聪明的车轮来得快:

with c(jb) as (values('{
  "date": "2018-02-12",
  "author": "devoplex",
  "block": [
    { "color": "#C70039", "title": "Fruit" },
    { "color": "#DAF7A6", "title": "Vegetable" },
    { "color": "#DAF7A6", "title": "Meat" }
  ]
}'::jsonb))
, m as (select jb,e,case when o <3 then o else o+1 end o from c, jsonb_array_elements(jb->'block') with ordinality t(e,o) union all select jb, '{ "color": "#581845", "title": "Candy" }',3 from c)
, n as (select distinct jb,jsonb_agg(e) over (order by o) a from m)
select jsonb_pretty(jsonb_set(jb,'{block}',a)) from n order by length(a::text) desc limit 1;
           jsonb_pretty
----------------------------------
 {                               +
     "date": "2018-02-12",       +
     "block": [                  +
         {                       +
             "color": "#C70039", +
             "title": "Fruit"    +
         },                      +
         {                       +
             "color": "#DAF7A6", +
             "title": "Vegetable"+
         },                      +
         {                       +
             "color": "#581845", +
             "title": "Candy"    +
         },                      +
         {                       +
             "color": "#DAF7A6", +
             "title": "Meat"     +
         }                       +
     ],                          +
     "author": "devoplex"        +
 }

当然你应该更换 3 如果你想在其他索引中使用其他数字。。。 http://dbfiddle.uk/?rdbms=postgres_10&fiddle=ccef24ef615b30eec07be9d1be5a1f8d 下面是从主查询中提取索引以指示CTE的示例