How to update every object inside a JSON array with PostgreSQL?

Question

I am trying to make a query that updates all the objects inside the nested json array i.e update first_name of all the people named 'John' to some other name.

My JSON is something like this but with way more data ofc:

{
  "_id": {
    "$oid": "5eb21a9f779aac987b2584b2"
  },
  "name": "Bouchard Restaurant and Inn",
  "cuisine": "Australian",
  "stars": 0.2,
  "address": {
    "street": "1602 Bosup Terrace",
    "city": "Sibharze",
    "state": "CA",
    "zipcode": "21875"
  },
  "reviews": [
    {
      "person": {
        "_id": {
          "$oid": "57d7a121fa937f710a7d486e"
        },
        "address": {
          "city": "Burgessborough",
          "street": "83248 Woods Extension",
          "zip": "47201"
        },
        "birthday": {
          "$date": "2011-03-17T11:21:36Z"
        },
        "email": "[email protected]",
        "first_name": "Yvonne",
        "job": "Counselling psychologist",
        "last_name": "Pham"
      },
      "comment": "Aliquam est reiciendis alias neque ad.",
      "created_on": {
        "$date": "2017-12-09T20:49:00.35Z"
      }
    },
    {
      "person": {
        "_id": {
          "$oid": "57d7a121fa937f710a7d486f"
        },
        "address": {
          "city": "Nicholsbury",
          "state": "Indiana",
          "street": "699 Ryan Branch Apt. 371",
          "zip": "52277"
        },
        "birthday": {
          "$date": "2015-11-25T17:26:40Z"
        },
        "email": "[email protected]",
        "first_name": "Mary",
        "job": "Conservator, furniture",
        "last_name": "Nelson"
      },
      "comment": "Quis sed tenetur eius illo.",
      "created_on": {
        "$date": "2020-01-03T16:55:51.396Z"
      }
    },
    {
      "person": {
        "_id": {
          "$oid": "57d7a121fa937f710a7d4870"
        },
        "address": {
          "city": "Crystalmouth",
          "street": "3924 Mosley Burg Suite 602",
          "zip": "14969"
        },
        "birthday": {
          "$date": "2015-04-07T19:10:04Z"
        },
        "email": "[email protected]",
        "first_name": "Jenna",
        "job": "Engineer, land",
        "last_name": "Smith"
      },
      "comment": "Recusandae rem minus dolorum corporis corrupti rem placeat.",
      "created_on": {
        "$date": "2019-06-13T13:00:34.473Z"
      }
    },
    {
      "person": {
        "_id": {
          "$oid": "57d7a121fa937f710a7d4871"
        },
        "address": {
          "city": "Lake Meaganton",
          "state": "Idaho",
          "street": "2831 Kevin Knolls",
          "zip": "10914-3394"
        },
        "birthday": {
          "$date": "2014-02-08T01:03:22Z"
        },
        "email": "[email protected]",
        "first_name": "Christopher",
        "job": "Investment banker, corporate",
        "last_name": "Franklin"
      },
      "comment": "Id provident eius natus quasi minima nobis.",
      "created_on": {
        "$date": "2016-01-05T02:15:06.933Z"
      }
    },
    {
      "person": {
        "_id": {
          "$oid": "57d7a121fa937f710a7d4872"
        },
        "address": {
          "city": "Morganport",
          "state": "Vermont",
          "street": "9069 Bailey Ferry Suite 423",
          "zip": "99473"
        },
        "birthday": {
          "$date": "2015-12-19T18:27:42Z"
        },
        "email": "[email protected]",
        "first_name": "Elizabeth",
        "job": "Theatre stage manager",
        "last_name": "Herrera"
      },
      "comment": "Sit perferendis nostrum suscipit cumque mollitia.",
      "created_on": {
        "$date": "2016-09-27T15:47:22.458Z"
      }
    }
  ]}

I have a query that updates the first object in the array successfully:

UPDATE restaurants
SET info = jsonb_set(info::jsonb, '{reviews,0,person,first_name}', '"Vedat"', true)
WHERE info->'reviews'->0->'person'->>'first_name' = 'John';

However, trying make an update query that updates all objects within the array (reviews) seems to be almost impossible.

I tried something like this:

UPDATE restaurants r
SET info = (
   SELECT jsonb_agg(jsonb_set(rev::jsonb, '{first_name}', rev -> 'person' ->> 'first_name' = '"Vedat"', false))
   FROM   jsonb_array_elements(r.info::jsonb->'reviews') rev
    WHERE rev -> 'person' ->> 'first_name' = 'John'
   );

But it wasn't successful, it gives me errors like:

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

UPDATE

I came up with this query but it runs inifitely

with rev as (
    select id, generate_series(0, jsonb_array_length(info::jsonb->'reviews')-1) i
    from   restaurants
)
update restaurants r
   set info = jsonb_set(r.info::jsonb, 
                 array['reviews', rev.i::varchar, 'first_name'], 
                 '"Vedat"'::jsonb)
from   rev
where  r.info->'reviews'->rev.i-> 'person' ->> 'first_name' = 'John'

Answer 1

The query gets a bit complicated but here is a solution, unless it matters that the order of elements in the .reviews array is not preserved:

update test_j
   set j = jsonb_set( -- Replace the '.reviews' array with a concatenation of two arrays,
                      -- one with the unchanged objects and one with the changed ones.
            j
          , '{reviews}'
          ,    jsonb_path_query_array( -- Extract the objects from the '.reviews' array
                                       -- that need _no_ updating.
                                       -- Uses the SQL/JSON Path Language. 
                   j
                 , '$.reviews[*] ? (@.person.first_name != "Jenna" && @.person.first_name != "Yvonne")'
               )
            || array_to_json(ARRAY( -- convert 'set of jsonb' to PostgreSQL array and back
                                    -- ( do not forget the cast from json to jsonb !)
                   select jsonb_set(  -- Update the json data
                              jsonb_path_query( -- Select the objects from the '.reviews' array
                                                -- that need updating
                                                -- (2 names chosen to demonstrate multiple updates 
                                                -- with the original sample data).
                                  j
                                , '$.reviews[*] ? (@.person.first_name == "Jenna" || @.person.first_name == "Yvonne")'
                              )
                            , '{person,first_name}'
                            , '"Vedat"'
                            , true
                          )
                     from test_j
               ))::jsonb
          , true
       )
     ;

Most likely there are more elegant and more efficient ways to get the job done but the code should get people started.

Online demo available here (DB fiddle).

The relevant section of the PostgreSQL 12 docs can be found here