Collection of queries to manipulate JSONB in PostgreSQL

postgresql-json-queries.md

CREATE TABLE users(
  id serial not null primary key,
  username text not null
);

CREATE TABLE tweets(
  id serial not null primary key,
  content jsonb default('{}')
);

INSERT INTO users(username) VALUES('tom'), ('jerry');

INSERT INTO tweets(content) 
VALUES
('{
    "text": "#PostgreSQL is awesome! Thanks @tom and @jerry for all the hard work :) #code",
    "mentions": [
        {"user_id": 1},
        {"user_id": 2}
    ],
    "hashtags": [
        "PostgreSQL",
        "code"
    ]
}');

Get mentioned users with username (join on JSONB array field)

SELECT 
  tweets.id, 
  -- build array of json objects for mentioned users
  json_agg(json_build_object('id', users.id, 'username', users.username)) as mentioned_users
FROM tweets
  -- lateral join to get list of mentions as JSONB object
  JOIN LATERAL jsonb_array_elements(content->'mentions') mentions ON true
  JOIN users ON (mentions->>'user_id')::int = users.id
GROUP BY tweets.id;

Remove mention with user_id 2

UPDATE tweets
-- Use the #- operator to delete an element from the array
SET content = content #- ('{mentions,' || (
  -- Determine the index of the element with user_id=2
  SELECT indx
  FROM generate_series(0, jsonb_array_length(content->'mentions') - 1) as indx
  WHERE (content->'mentions'->indx->'user_id' = '2')
) || '}')::text[];

Converting to and from JSON

CREATE OR REPLACE FUNCTION text_to_json(value text)
RETURNS text AS $$
BEGIN
  return value::json;
EXCEPTION
  WHEN others THEN
    return to_json(value);
END;
$$ language plpgsql;
    
CREATE OR REPLACE FUNCTION json_to_text(value text)
RETURNS text AS $$
BEGIN
  return value::json #>> '{}';
EXCEPTION
  WHEN others THEN
    return value;
END;
$$ language plpgsql;