Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT *
  FROM (VALUES ('[{
    "blah": "blah",
    "owners": [
        "admin"
    ]},
{
    "blah": "blah",
    "owners": [
        "john@google.com"
    ]},
{
    "blah": "blah",
    "owners": [
        "jason@facebook.com", "fred@facebook.com"
    ]},
{
    "blah": "blah",
    "owners": [
        "zuck@facebook.com", "bill@google.com"
    ]}]' :: jsonb)) AS json_data


column1


[{"blah": "blah", "owners": ["admin"]}, {"blah": "blah", "owners": ["john@google.com"]}, {"blah": "blah", "owners": ["jason@facebook.com", "fred@facebook.com"]}, {"blah": "blah", "owners": ["zuck@facebook.com", "bill@google.com"]}]



SELECT 1


CREATE OR REPLACE FUNCTION jsonb_set(x jsonb, y jsonb, p text[], z jsonb, c boolean)
RETURNS jsonb LANGUAGE sql AS
$$
  SELECT jsonb_set(COALESCE(x,y), p, z, c) ;
$$ ;

CREATE OR REPLACE AGGREGATE jsonb_set_agg(x jsonb,p text[], z jsonb, c boolean)
(SFUNC = jsonb_set, STYPE = jsonb) ;

CREATE FUNCTION



CREATE AGGREGATE


SELECT jsonb_set_agg(j.json_data, array[(b.id - 1) :: text, 'owners', (c.id - 1) :: text], to_jsonb('insert_here_the_new_value' :: text), true)
  FROM (VALUES ('[{
    "blah": "blah",
    "owners": [
        "admin"
    ]},
{
    "blah": "blah",
    "owners": [
        "john@google.com"
    ]},
{
    "blah": "blah",
    "owners": [
        "jason@facebook.com", "fred@facebook.com"
    ]},
{
    "blah": "blah",
    "owners": [
        "zuck@facebook.com", "bill@google.com"
    ]}]' :: jsonb)) AS j(json_data)
 CROSS JOIN LATERAL jsonb_array_elements(j.json_data) WITH ORDINALITY AS b(json_data, id)
 CROSS JOIN LATERAL jsonb_array_elements_text(b.json_data->'owners') WITH ORDINALITY AS c(json_data, id)
 WHERE c.json_data <> 'admin'
   AND NOT c.json_data ~ '@google.com$'


jsonb_set_agg


[{"blah": "blah", "owners": ["admin"]}, {"blah": "blah", "owners": ["john@google.com"]}, {"blah": "blah", "owners": ["insert_here_the_new_value", "insert_here_the_new_value"]}, {"blah": "blah", "owners": ["insert_here_the_new_value", "bill@google.com"]}]



SELECT 1