current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.3 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 0 down vote favorite

I am using PostgreSQL 9.4.1 and have run into an issue with a column that I need to update. The column is of type JSON with elements in the following format:

["a","b","c",...]
["a","c","d",...]
["c","d","e",...]

etc.

so that each element is a string. It is my understanding that each of these elements are considered keys to the JSON array (please correct me if I am a bit confused here, I haven't ever worked with JSON datatype columns before, so I'm still trying to get a grip on them anyways). There is not an actual pattern to these arrays, and their contents are dependent on user input from somewhere else. My goal is to update any of the arrays that contain a particular element (say "b" for the purpose of explaining my question more thoroughly) and replace the content "b" with say "b1". Meaning that:

["a","b","c",...]

would be updated to

["a","b1","c",...]

I have found a few ways listed on this site (I don't currently have the links, but I can find them again if necessary) to update VALUES for a particular KEY, but I haven't found a way mentioned to change the KEY itself. I have already found a way to target the specific rows of interest by doing something similar to:

    SELECT * 
    FROM TableA 
    WHERE column::json ?| ["b", other string elements of interest]

Any suggestions would be greatly appreciated. Thanks for your help!

share|improve this question
up vote 0 down vote accepted

Basically, solution is to go over the list of json_array_elements, and based on json value using CASE condition replace certain value with other one. After all, need to re-build new json array using array_agg() and to_json() description of aggregate functions in psql is here. Possible query can be the following:

-- Sample DDL and JSON data
CREATE TABLE jsontest (data JSON);
INSERT INTO jsontest VALUES ('["a","b","c"]'::JSON);
-- QUERY
WITH result AS (
  SELECT to_json( -- creating updated json structure
             array_agg( -- create array with new element "b1"
                 CASE WHEN element::TEXT = '"b"' -- here we process array elements to find "b"
                   THEN to_json('b1'::TEXT)
                   ELSE element
                 END)) as new_json
  FROM jsontest,json_array_elements(jsontest.data) as element
)
UPDATE jsontest SET data = result.new_json FROM result;
share|improve this answer
up vote 0 down vote

So I went ahead and gave that a check (because it looks like it should work, and it's more or less what I ended up doing), but I figured out what I was trying to do! What I got was this:

Update Table A
SET column = replace(column::TEXT,'b','b1')::json
WHERE column::json ?| ['b']

And now that I think about it, I probably don't even need the last where condition because the replace won't affect anything that doesn't have 'b' in it. But that worked for me, and it looks like yours probably should too! Thanks for the help!

share|improve this answer

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.