DEV Community

Uday Yadav
Uday Yadav

Posted on • Updated on

SQL : JSON Datatype

#sql #programming #database

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)
Enter fullscreen mode Exit fullscreen mode

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"]}
Enter fullscreen mode Exit fullscreen mode

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"}
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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"}
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode

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}
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

Latest comments (0)

Read next

guto profile image

Meet Datomic: the immutable and functional database.

guto -

tyaga001 profile image

🔥 6 Open-source Libraries You Must Know in 2024 🪄🔥

Ankur Tyagi -

jindalanuj profile image

Say Goodbye to ElasticSearch: Discover MongoDB's Powerful Search Capabilities

Anuj Jindal -

codemouse92 profile image

SQL Injection Isn't Dead Yet

Jason C. McDonald -