current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I have a table like this;

CREATE TABLE test (
  id BIGSERIAL PRIMARY KEY,
  data JSONB
);

INSERT INTO test(data) VALUES('[1,2,"a",4,"8",6]'); -- id = 1
INSERT INTO test(data) VALUES('[1,2,"b",4,"7",6]'); -- id = 2

How to update element data->1 and data->3 into something else without PL/*?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

You cannot manipulate selected elements of a json / jsonb type directly. Functionality for that is still missing in Postgres 9.4 (see @Craig's comment). You have to do 3 steps:

  1. Unnest / decompose the JSON value.
  2. Manipulate selected elements.
  3. Aggregate / compose the value back again.

To replace the 3rd element of the json array (data->3) in the row with id = 1 with a given (new) value ('<new_value>') in pg 9.4:

UPDATE test t
SET    data = t2.data
FROM  (
   SELECT id, array_to_json(
                 array_agg(CASE WHEN rn = 1 THEN '<new_value>' ELSE elem END))
              ) AS data
   FROM   test t2
        , json_array_elements_text(t2.data) WITH ORDINALITY x(elem, rn)         
   WHERE  id = 1
   GROUP  BY 1
   ) t2
WHERE  t.id = t2.id
AND    t.data <> t2.data; -- avoid empty updates

About json_array_elements_text():

About WITH ORDINALITY:

share|improve this answer
2  
Functions to transform jsonb values should be coming in 9.5. There's nothing inherently different to any kind of other function that takes inputs and produces outputs. It's mostly that nobody's implemented functions to replace elements, etc, yet. –  Craig Ringer Dec 9 '14 at 13:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.