How to update multiple nested keys in a json field in single update query?

Question

I'm trying to write a function that updates a json (not jsonb) field (called settings) in a table (called user).

My json object looks like this (however it might not have some of the keys on any nesting level):

{
  "audiences": ["val1", "val2"],
  "inviteNumber": "123",
  "workExperience": [
    {
      "company": {
        "name": "Ace",
        "industry": "Accounting",
        "revenues": "1M-10M",
        "size": "1-10"
      },
      "title": "Product",
      "startDate": {
        "month": 1,
        "year": 2018
      }
    }
  ],
  "notifications": {
    "emailNotifications": true
  },
  "jobFunction": "Research & Development",
  "phoneNumber": "2134447777",
  "areasOfInterest": {
    "Recruiting and Staffing": true
  }
}

I need to be able to update the "title" and "name" fields of the 0th element inside "workExperience" array.

What I currently have is this:

create or replace function my_function()
returns trigger language plpgsql as $$
declare
    companyName character varying(255);
begin
    select company.name into companyName from company where id = new.companyid;

    update "user" set
        email = new.email,
        "settings" = jsonb_set(
            "settings"::jsonb,
            '{workExperience,0}'::TEXT[],
            format(
                '{"company": {"name": %s}, "title": %s}',
                '"' || companyName || '"', '"' || new.title || '"'
            )::jsonb
        )
    where id = new.userid;
    
    return new;
end $$;

However the above implementation rewrites the while workExperience object removing the keys other than company and title.

I've tried looking through this answer on SO, but still wasn't able to implement the updating correctly.

I see that the problem is with how the jsonb_set works and it does just what I tell it to do: set 0th element of "workExperience" array to the object I define inside format function. Seems like I need to use multiple jsonb_set one inside another, but I can't figure out the way to do it correctly.

How can I update my json field correctly without removing other keys from the object?

score 3 · Accepted Answer · answered Jan 22 '21 at 09:15

3

jsonb_set() returns the modified JSON value.

You could nest the calls and change the company name first, and use the result of that as the input to another jsonb_set().

"settings" = jsonb_set(jsonb_set("settings"::jsonb, '{workExperience,0,company,name}', to_jsonb(companyname)), 
                       '{workExperience,0,title}', to_jsonb(new.title)
                      )

answered Jan 22 '21 at 09:15

For some reason it gives me `could not determine polymorphic type because input has type "unknown"` without much context. I've tried casting paths by doing `::TEXT[]` but to no avail – Denis Yakovenko Jan 22 '21 at 09:47
2

It's most likely the `to_jsonb()` function. Try `to_jsonb(new.title::text)` and `to_jsonb(companyname::text)` – Jan 22 '21 at 09:48
Brilliant, it worked! Thanks a lot. I've always struggled with this type casting problems – Denis Yakovenko Jan 22 '21 at 09:51

How to update multiple nested keys in a json field in single update query?

1 Answers1