How do I modify a single property value inside the PostgreSQL JSONB datatype?

Question

How do I modify a single field inside the PostgreSQL JSONB datatype?

Let's say I have a table called animal like this:

id       info
------------------------------------------------------------
49493   {"habit1":"fly","habit2":"dive","location":"SONOMA NARITE"}

I'd like to simply change value(say, to upper case or lower case the text) of the location property. so the result, after UPDATE is

    id       info
------------------------------------------------------------
49493   {"habit1":"fly","habit2":"dive","location":"sonoma narite"}

I tried this below and it does not work

update animal set info=jsonb_set(info, '{location}', LOWER(info->>'location'), true) where id='49493';
----------------------------------
ERROR:  function jsonb_set(jsonb, unknown, text, boolean) does not exist
LINE 7: update animal set info=jsonb_set(info, '{con...
                                           ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
********** Error **********

ERROR: function jsonb_set(jsonb, unknown, text, boolean) does not exist

if I simply know what the updated value would be then I can use just use this:

update animal set info=jsonb_set(info, '{location}', '"sonoma narite"', true) where id='49493';

However, if the text value is unknown and we just want to do some simply operation such as append, prepend, upper/lower case, I can't simply find an answer to it.

I was surprised by the fact that jsonb set function does not offer such a trivial operation that only try to update the case of a text property inside a jsonb.

Can someone help?

I tried update test_table set info=jsonb_set(info, '{location}', '"cityTest"', true) where id='123'; but its still giving error as function does not exist. I'm using postgresql 9.4.8 — nikhil84
– nikhil84, Commented Aug 1, 2017 at 12:54

klin · Accepted Answer · 2017-02-01 00:26:43Z

22

The third argument of jsonb_set() should be of jsonb type. The problem is in casting a text string to jsonb string, you need a string in double quotes. You can use concat() or format():

update animal
set info = 
    jsonb_set(info, '{location}', concat('"', lower(info->>'location'), '"')::jsonb, true) 
--  jsonb_set(info, '{location}', format('"%s"', lower(info->>'location'))::jsonb, true) 
where id='49493'
returning *;

  id   |                               info                               
-------+------------------------------------------------------------------
 49493 | {"habit1": "fly", "habit2": "dive", "location": "sonoma narite"}
(1 row)

In Postgres 9.4 you should unnest the json column using jsonb_each_text(), aggregate keys and values modifying the proper value on the fly, and finally build a json object:

update animal a
set info = u.info
from (
    select id, json_object(
        array_agg(key), 
        array_agg(
            case key when 'location' then lower(value)
            else value end))::jsonb as info
    from animal,
    lateral jsonb_each_text(info) 
    group by 1
    ) u
where u.id = a.id
and a.id = 49493;

If you can create functions this solution might be more pleasant:

create or replace function update_info(info jsonb)
returns jsonb language sql as $$
    select json_object(
        array_agg(key), 
        array_agg(
            case key when 'location' then lower(value)
            else value end))::jsonb
    from jsonb_each_text(info)
$$

update animal
set info = update_info(info)
where id = 49493;

edited Feb 1, 2017 at 0:26

answered Jan 27, 2017 at 20:28

klin

123k15 gold badges240 silver badges262 bronze badges

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

9 Comments

vichsu Over a year ago

Thanks! Klin, I will try that and let you know.

klin Over a year ago

There is no token "someooopath" in my query. I'm not able to debug a query which I cannot see...

vichsu Over a year ago

Hi klin, I think I found the issue in which it does not work. let's say if you have a underscore in the "location" property in the example above. postgres will throw error like "invalid input syntax for type json". Or if the value of this property does not have a space in it. It will throw error as well. # This value under info column does not work {"location":"MOCKUP_ATTRIBUTE"} # This value under info column does not work either {"location":"MOCKUPATTRIBUTE"} # This value under info column will WORK {"location":"MOCKUP ATTRIBUTE"}

klin Over a year ago

Yes, you're right. In these cases quote_ident() is not a good choice. Use concat() or format() instead. See the modified answer.

vichsu Over a year ago

Hi klin, I faced another issue with this. My dev machine has Postgres v9.6 which has jsonb_set function but our production server is using Postgres v9.4.5 which does not have jsonb_set function. So I can't use this script. if you don't mind, would you help with or give me some suggestion about it? Thanks!

|

vichsu · Accepted Answer · 2017-01-31 01:05:35Z

5

Some good news here I'd like to share. Thanks to klin, His input helped me discover this solution. In example above. if I simply use concat function, then the issue that I found in the code that klin posted is resolved (in short, it works only if the text value contains spaces). Now I can lower case a single property value!

UPDATE test1 set info=jsonb_set(info, '{location}', concat('"',lower(info->>'locatioin'),'"')::jsonb, true) returning *;

answered Jan 31, 2017 at 1:05

vichsu

1,9005 gold badges17 silver badges20 bronze badges

Collectives™ on Stack Overflow

How do I modify a single property value inside the PostgreSQL JSONB datatype?

2 Answers 2

9 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related