Add array at specific index/position in jsonb Postgresql field

Question

I'm wondering if there's a way to append array to a specific index/position in an jsonb array in Postgresql 9.6?

Let's imagine that my code below is my json:

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

I want to append this object in my "block" array:

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

But I want this object to become the third index/position without replacing anything. So finally I can have this result:

{
  "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
  ]
}

This example in not my actual code but it's the same issue. This is for the construction of a form, so it need to be in a specific order or else it won't make any sense. Thanks you.

Vao Tsun · Accepted Answer · 2018-02-12 14:43:32Z

1

an ugly wheel, but nothing smarter comes fast:

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"        +
 }

of course you should replace 3 with other digit if you want it in other index... http://dbfiddle.uk/?rdbms=postgres_10&fiddle=ccef24ef615b30eec07be9d1be5a1f8d here's example with index taken out of main query to ind CTE

answered Feb 12, 2018 at 14:43

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Pøziel Over a year ago

It seems to work when the select operator! But what should I do if I want to update my form field in my created_form table?

Vao Tsun Over a year ago

that's a different question, but in short use with cte as blah update from cte where construct

Pøziel Over a year ago

Thanks for your reply, but I found another way to solve my problem through PHP. I'm not an expert in database query, so that is easier to read for me. :)

Collectives™ on Stack Overflow

Add array at specific index/position in jsonb Postgresql field

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related