How to delete a jsonb item in a nested psql array

Question

I have a table users:

`CREATE TABLE users(
    id SERIAL PRIMARY KEY NOT NULL,
    username TEXT UNIQUE,
    saved_articles JSONB[],
  )`

I added a user like so:

      "INSERT INTO users (username, saved_articles) VALUES (?, array[]::jsonb[]) RETURNING id, username, saved_articles"

After adding some articles I have this data shape:

{ id: 1,
  username: 'test',
  saved_articles:
   [ { url: 'test',
       title: '',
       author: '',
       source: '',
       content:"",
       urlToImage: '' 
     },
     { url: 'not-test',
       title: '',
       author: '',
       source: '',
       content:"",
       urlToImage: '' 
     }
   ]
}

I want to be able to delete a specific item from the saved_articles array based on the url value.

For example, if my url value is 'test', after running the query my data should look like:

{ id: 1,
  username: 'test',
  saved_articles:
   [ { url: 'not-test',
       title: '',
       author: '',
       source: '',
       content:"",
       urlToImage: '' 
     }
   ]
}

Your example data is not valid JSONB[]. Nor valid JSON for that matter. — jjanes
– jjanes, Commented Apr 18, 2020 at 12:15

Barbaros Özhan · Accepted Answer · 2020-04-19 14:53:28Z

First of all, the format of JSONB columns's value should be fixed. That might be tested through CASTing AS JSONB by a SELECT statement such as

SELECT '{ "id": "1",
  "username": "test",
  "saved_articles":
   [ { "url": "test",
       "title": "",
       "author": "",
       "source": "",
       "content":"",
       "urlToImage": "" 
     },
     { "url": "not-test",
       "title": "",
       "author": "",
       "source": "",
       "content":"",
       "urlToImage": "" 
     }
   ]}'::jsonb

whether returns error or not.

Then, remove the desired element from the array by use of jsonb_array_elements(json_data -> 'saved_articles') function together with ->> 'url' != 'test' criteria.

And then reconstruct the array by remaining elements by using jsonb_build_array and jsonb_object_agg.

At the last step concatenate the part which doesn't contain that individual array extracted by json_data #- '{saved_articles}' :

SELECT js0||jsonb_object_agg( 'saved_articles', js1 ) AS "Result JSONB"
  FROM
  (
   SELECT json_data #- '{saved_articles}' AS js0, jsonb_build_array( js ) AS js1
     FROM tab
    CROSS JOIN jsonb_array_elements(json_data -> 'saved_articles') js
    WHERE js ->> 'url' != 'test'
   ) q
 GROUP BY js0

Demo

Collectives™ on Stack Overflow

How to delete a jsonb item in a nested psql array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related