SQL : JSON Datatype

This guide is for PostgreSQL : https://www.postgresql.org/

Getting Started with PostgreSQL :
https://dev117uday.gitbook.io/databases/sql/getting-started

Loading Sample Data Guide :
https://dev117uday.gitbook.io/databases/sql/getting-started/load-data

JSON vs JSONB

JSON	JSONB
stores data in text format	stores data in binary format
stores data as it is	trims of white spaces
slower in operations	faster in operations
doesn't support full text indexing	supports full text indexing

SELECT '{
    "title":"book 1"}
'::json;

         json          
-----------------------
 {                    +
     "title":"book 1"}+

(1 row)


SELECT '
  {"title":"book 1"}
  '::jsonb


        jsonb        
---------------------
 {"title": "book 1"}
(1 row)

Operations

CREATE TABLE books_jsonb
(
    id        serial primary key,
    book_info JSONB
);

INSERT INTO books_jsonb (book_info)
VALUES ('{
  "title": "Book 1"
}'),
('{
  "title": "Book 2"
}'),
('{
  "title": "Book 3"
}');

 id | title  
----+--------
  1 | Book 1
  2 | Book 2
  3 | Book 3


SELECT id, book_info ->> 'title' AS "title"
FROM books_jsonb
WHERE book_info ->> 'title' = 'Book 1';

 id | title  
----+--------
  1 | Book 1


INSERT INTO books_jsonb (book_info)
VALUES ('{ "title": "Book 10" }');


 id |      book_info       
----+----------------------
  1 | {"title": "Book 1"}
  2 | {"title": "Book 2"}
  3 | {"title": "Book 3"}
  4 | {"title": "Book 10"}


UPDATE books_jsonb
SET book_info = book_info || '{"title": "Book 4" }'
WHERE book_info ->> 'title' = 'Book 10';

 id |      book_info      
----+---------------------
  1 | {"title": "Book 1"}
  2 | {"title": "Book 2"}
  3 | {"title": "Book 3"}
  4 | {"title": "Book 4"}


UPDATE books_jsonb
SET book_info = book_info || '{"author": "author 1" }'
WHERE book_info ->> 'title' = 'Book 1';

 id |                 book_info                 
----+-------------------------------------------
  2 | {"title": "Book 2"}
  3 | {"title": "Book 3"}
  4 | {"title": "Book 4"}
  1 | {"title": "Book 1", "author": "author 1"}


UPDATE books_jsonb
SET book_info = book_info - 'author'
WHERE book_info ->> 'title' = 'Book 1';

 id |      book_info      
----+---------------------
  1 | {"title": "Book 1"}
  2 | {"title": "Book 2"}
  3 | {"title": "Book 3"}
  4 | {"title": "Book 4"}


UPDATE books_jsonb
SET book_info = book_info || '{"available":["new delhi","Tokyo","sydney"]}'
WHERE book_info ->> 'title' = 'Book 1';

 id |  book_info                                         

  2 | {"title": "Book 2"}
  3 | {"title": "Book 3"}
  4 | {"title": "Book 4"}
  1 | {"title": "Book 1", "author": "author 1", "available": ["new delhi", "Tokyo", "sydney"]}


UPDATE books_jsonb
SET book_info = book_info #- '{available,1}'
WHERE book_info ->> 'title' = 'Book 1';

 id |   Book_info                                    

  2 | {"title": "Book 2"}
  3 | {"title": "Book 3"}
  4 | {"title": "Book 4"}
  1 | {"title": "Book 1", "author": "author 1", "available": ["new delhi", "sydney"]}

ROW_TO_JSON()

SELECT row_to_json(orders)
FROM orders;

 {"order_id":10248,"customer_id":"VINET","employee_id":5,"order_date":"1996-07-04","required_date":"1996-08-01","shipped_date":"1996-07-16","ship_via":3,"freight":32.38,"ship_name":"Vins et alcools Chevalier","ship_address":"59 rue de l'Abbaye","ship_city":"Reims","ship_region":null,"ship_postal_code":"51100","ship_country":"France"}

SELECT row_to_json(t)
FROM 
(
  SELECT *
  FROM orders
) AS t;

 {"order_id":10248,"customer_id":"VINET","employee_id":5,"order_date":"1996-07-04","required_date":"1996-08-01","shipped_date":"1996-07-16","ship_via":3,"freight":32.38,"ship_name":"Vins et alcools Chevalier","ship_address":"59 rue de l'Abbaye","ship_city":"Reims","ship_region":null,"ship_postal_code":"51100","ship_country":"France"}

JSON_AGG()

SELECT *
FROM orders;

SELECT director_id, first_name, lASt_name, 
(
  SELECT json_agg(x)
  FROM 
    (
        SELECT movie_name
        FROM movies mv
        WHERE mv.director_id = directors.director_id
    ) AS x
) :: jsonb
FROM directors;

JSON_BUILD

SELECT json_build_array(1, 2, 3, 4, 5, 6);

  json_build_array  
--------------------
 [1, 2, 3, 4, 5, 6]


SELECT json_build_array(1, 2, 3, 4, 5, 6, 'Hi');

     json_build_array     
--------------------------
 [1, 2, 3, 4, 5, 6, "Hi"]


-- error : argument list must have even number of elements
SELECT json_build_object(1, 2, 3, 4, 5);

SELECT json_build_object(1, 2, 3, 4, 5, 6, 7, 'Hi');

            json_build_object            
-----------------------------------------
 {"1" : 2, "3" : 4, "5" : 6, "7" : "Hi"}


SELECT json_object('{name,email}', '{"adnan","a@b.com"}');

               json_object               
-----------------------------------------
 {"name" : "adnan", "email" : "a@b.com"}

Json Functions

CREATE TABLE directors_docs
(
    id   serial primary key,
    body jsonb
);


SELECT director_id,
       first_name,
       last_name,
       (
           SELECT json_agg(x) AS all_movies
           FROM (
                    SELECT movie_name
                    FROM movies mv
                    WHERE mv.director_id = directors.director_id
                ) x
       ) :: jsonb
FROM directors;


INSERT INTO directors_docs (body)
SELECT row_to_json(a)
FROM (
         SELECT director_id,
                first_name,
                last_name,
                (
                    SELECT json_agg(x) AS all_movies
                    FROM (
                             SELECT movie_name
                             FROM movies mv
                             WHERE mv.director_id = directors.director_id
                         ) x
                ) :: jsonb
         FROM directors
) AS a;

SELECT *
FROM directors_docs LIMIT 3;

  1 | {"last_name": "Alfredson", "all_movies": [{"movie_name": "Let the Right One In"}], "first_name": "Tomas", "director_id": 1}
  2 | {"last_name": "Anderson", "all_movies": [{"movie_name": "There Will Be Blood"}], "first_name": "Paul", "director_id": 2}
  3 | {"last_name": "Anderson", "all_movies": [{"movie_name": "Grand Budapest Hotel"}, {"movie_name": "Rushmore"}, {"movie_name": "The Darjeeling Limited"}], "first_name": "Wes", "director_id": 3}


SELECT *, jsonb_array_length(body -> 'all_movies') AS total_movies
FROM directors_docs
order by jsonb_array_length(body->'all_movies') DESC;

 13 | {"last_name": "Kubrick", "all_movies": [{"movie_name": "A Clockwork Orange"}, {"movie_name": "Eyes Wide Shut"}, {"movie_name": "The Shining"}], "first_name": "Stanley", "director_id": 13}                                   |            3
  3 | {"last_name": "Anderson", "all_movies": [{"movie_name": "Grand Budapest Hotel"}, {"movie_name": "Rushmore"}, {"movie_name": "The Darjeeling Limited"}], "first_name": "Wes", "director_id": 3}                                |            3
 17 | {"last_name": "Lucas", "all_movies": [{"movie_name": "Star Wars: A New Hope"}, {"movie_name": "Star Wars: Empire Strikes Back"}, {"movie_name": "Star Wars: Return of the Jedi"}], "first_name": "George", "director_id": 17} |            3


SELECT *,jsonb_object_keys(body) FROM directors_docs;

  1 | {"last_name": "Alfredson", "all_movies": [{"movie_name": "Let the Right One In"}], "first_name": "Tomas", "director_id": 1} | last_name
  1 | {"last_name": "Alfredson", "all_movies": [{"movie_name": "Let the Right One In"}], "first_name": "Tomas", "director_id": 1} | all_movies
  1 | {"last_name": "Alfredson", "all_movies": [{"movie_name": "Let the Right One In"}], "first_name": "Tomas", "director_id": 1} | first_name


SELECT j.key, j.value
FROM directors_docs,
     jsonb_each(body) j;

    key     |                  value                   
------------+------------------------------------------
 last_name  | "Alfredson"
 all_movies | [{"movie_name": "Let the Right One In"}]
 first_name | "Tomas"

Existence Operators

SELECT *
FROM directors_docs
WHERE body -> 'first_name' ? 'John';

 14 | {"last_name": "Lasseter", "all_movies": [{"movie_name": "Toy Story"}], "first_name": "John", "director_id": 14}

Searching JSON

SELECT *
FROM directors_docs
WHERE body @> '{"first_name":"John"}';


SELECT *
FROM directors_docs
WHERE body @> '{"director_id":1}';

-- error : No operator matches the given name and argument types. You might need to add explicit type casts.
SELECT *
FROM directors_docs
WHERE body -> 'first_name' LIKE 'J%';


SELECT *
FROM directors_docs
WHERE body ->> 'first_name' LIKE 'J%';

SELECT *
FROM directors_docs
WHERE (body ->> 'director_id')::integer in (1,2,3,4,5,10);

Blog

Uday Yadav

JSON vs JSONB

Operations

ROW_TO_JSON()

JSON_AGG()

JSON_BUILD

Json Functions

Existence Operators

Searching JSON

Join Our Newsletter. No Spam, Only the good stuff.

Related