JSON
the usual

JSON

JSON vs JSONB

JSON
JSONB
stores data in text format
stores data in binary format
stores data AS-is
trims of white spaces
slower in operations
fASter in operations
doesn't support full text indexing
supports full text indexing
1
SELECT '{
2
"title":"book 1"}
3
'::json;
4
​
5
json
6
-----------------------
7
{ +
8
"title":"book 1"}+
9
​
10
(1 row)
11
​
12
​
13
SELECT '
14
{"title":"book 1"}
15
'::jsonb
16
​
17
​
18
jsonb
19
---------------------
20
{"title": "book 1"}
21
(1 row)
Copied!

Operations

1
CREATE TABLE books_jsonb
2
(
3
id serial primary key,
4
book_info JSONB
5
);
6
​
7
INSERT INTO books_jsonb (book_info)
8
VALUES ('{
9
"title": "Book 1"
10
}'),
11
('{
12
"title": "Book 2"
13
}'),
14
('{
15
"title": "Book 3"
16
}');
17
​
18
id | title
19
----+--------
20
1 | Book 1
21
2 | Book 2
22
3 | Book 3
23
​
24
​
25
SELECT id, book_info ->> 'title' AS "title"
26
FROM books_jsonb
27
WHERE book_info ->> 'title' = 'Book 1';
28
​
29
id | title
30
----+--------
31
1 | Book 1
32
​
33
​
34
INSERT INTO books_jsonb (book_info)
35
VALUES ('{ "title": "Book 10" }');
36
​
37
​
38
id | book_info
39
----+----------------------
40
1 | {"title": "Book 1"}
41
2 | {"title": "Book 2"}
42
3 | {"title": "Book 3"}
43
4 | {"title": "Book 10"}
44
​
45
​
46
UPDATE books_jsonb
47
SET book_info = book_info || '{"title": "Book 4" }'
48
WHERE book_info ->> 'title' = 'Book 10';
49
​
50
id | book_info
51
----+---------------------
52
1 | {"title": "Book 1"}
53
2 | {"title": "Book 2"}
54
3 | {"title": "Book 3"}
55
4 | {"title": "Book 4"}
56
​
57
​
58
UPDATE books_jsonb
59
SET book_info = book_info || '{"author": "author 1" }'
60
WHERE book_info ->> 'title' = 'Book 1';
61
​
62
id | book_info
63
----+-------------------------------------------
64
2 | {"title": "Book 2"}
65
3 | {"title": "Book 3"}
66
4 | {"title": "Book 4"}
67
1 | {"title": "Book 1", "author": "author 1"}
68
​
69
​
70
UPDATE books_jsonb
71
SET book_info = book_info - 'author'
72
WHERE book_info ->> 'title' = 'Book 1';
73
​
74
id | book_info
75
----+---------------------
76
1 | {"title": "Book 1"}
77
2 | {"title": "Book 2"}
78
3 | {"title": "Book 3"}
79
4 | {"title": "Book 4"}
80
​
81
​
82
UPDATE books_jsonb
83
SET book_info = book_info || '{"available":["new delhi","Tokyo","sydney"]}'
84
WHERE book_info ->> 'title' = 'Book 1';
85
​
86
id | book_info
87
​
88
2 | {"title": "Book 2"}
89
3 | {"title": "Book 3"}
90
4 | {"title": "Book 4"}
91
1 | {"title": "Book 1", "author": "author 1", "available": ["new delhi", "Tokyo", "sydney"]}
92
​
93
​
94
UPDATE books_jsonb
95
SET book_info = book_info #- '{available,1}'
96
WHERE book_info ->> 'title' = 'Book 1';
97
​
98
id | Book_info
99
​
100
2 | {"title": "Book 2"}
101
3 | {"title": "Book 3"}
102
4 | {"title": "Book 4"}
103
1 | {"title": "Book 1", "author": "author 1", "available": ["new delhi", "sydney"]}
Copied!

ROW_TO_JSON()

1
SELECT row_to_json(orders)
2
FROM orders;
3
​
4
{"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"}
5
​
6
SELECT row_to_json(t)
7
FROM
8
(
9
SELECT *
10
FROM orders
11
) AS t;
12
​
13
{"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"}
Copied!

JSON_AGG()

1
SELECT *
2
FROM orders;
3
​
4
SELECT director_id, first_name, lASt_name,
5
(
6
SELECT json_agg(x)
7
FROM
8
(
9
SELECT movie_name
10
FROM movies mv
11
WHERE mv.director_id = directors.director_id
12
) AS x
13
) :: jsonb
14
FROM directors;
Copied!

JSON_BUILD

1
SELECT json_build_array(1, 2, 3, 4, 5, 6);
2
​
3
json_build_array
4
--------------------
5
[1, 2, 3, 4, 5, 6]
6
​
7
​
8
SELECT json_build_array(1, 2, 3, 4, 5, 6, 'Hi');
9
​
10
json_build_array
11
--------------------------
12
[1, 2, 3, 4, 5, 6, "Hi"]
13
​
14
​
15
-- error : argument list must have even number of elements
16
SELECT json_build_object(1, 2, 3, 4, 5);
17
​
18
SELECT json_build_object(1, 2, 3, 4, 5, 6, 7, 'Hi');
19
​
20
json_build_object
21
-----------------------------------------
22
{"1" : 2, "3" : 4, "5" : 6, "7" : "Hi"}
23
​
24
​
25
SELECT json_object('{name,email}', '{"adnan","[email protected]"}');
26
​
27
json_object
28
-----------------------------------------
29
{"name" : "adnan", "email" : "[email protected]"}
Copied!

Json Functions

1
CREATE TABLE directors_docs
2
(
3
id serial primary key,
4
body jsonb
5
);
6
​
7
​
8
SELECT director_id,
9
first_name,
10
last_name,
11
(
12
SELECT json_agg(x) AS all_movies
13
FROM (
14
SELECT movie_name
15
FROM movies mv
16
WHERE mv.director_id = directors.director_id
17
) x
18
) :: jsonb
19
FROM directors;
20
​
21
​
22
INSERT INTO directors_docs (body)
23
SELECT row_to_json(a)
24
FROM (
25
SELECT director_id,
26
first_name,
27
last_name,
28
(
29
SELECT json_agg(x) AS all_movies
30
FROM (
31
SELECT movie_name
32
FROM movies mv
33
WHERE mv.director_id = directors.director_id
34
) x
35
) :: jsonb
36
FROM directors
37
) AS a;
38
​
39
SELECT *
40
FROM directors_docs LIMIT 3;
41
​
42
1 | {"last_name": "Alfredson", "all_movies": [{"movie_name": "Let the Right One In"}], "first_name": "Tomas", "director_id": 1}
43
2 | {"last_name": "Anderson", "all_movies": [{"movie_name": "There Will Be Blood"}], "first_name": "Paul", "director_id": 2}
44
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}
45
​
46
​
47
SELECT *, jsonb_array_length(body -> 'all_movies') AS total_movies
48
FROM directors_docs
49
order by jsonb_array_length(body->'all_movies') DESC;
50
​
51
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
52
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
53
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
54
​
55
​
56
SELECT *,jsonb_object_keys(body) FROM directors_docs;
57
​
58
1 | {"last_name": "Alfredson", "all_movies": [{"movie_name": "Let the Right One In"}], "first_name": "Tomas", "director_id": 1} | last_name
59
1 | {"last_name": "Alfredson", "all_movies": [{"movie_name": "Let the Right One In"}], "first_name": "Tomas", "director_id": 1} | all_movies
60
1 | {"last_name": "Alfredson", "all_movies": [{"movie_name": "Let the Right One In"}], "first_name": "Tomas", "director_id": 1} | first_name
61
​
62
​
63
SELECT j.key, j.value
64
FROM directors_docs,
65
jsonb_each(body) j;
66
​
67
key | value
68
------------+------------------------------------------
69
last_name | "Alfredson"
70
all_movies | [{"movie_name": "Let the Right One In"}]
71
first_name | "Tomas"
Copied!

Existence Operators

1
SELECT *
2
FROM directors_docs
3
WHERE body -> 'first_name' ? 'John';
4
​
5
14 | {"last_name": "Lasseter", "all_movies": [{"movie_name": "Toy Story"}], "first_name": "John", "director_id": 14}
Copied!

Searching JSON

1
SELECT *
2
FROM directors_docs
3
WHERE body @> '{"first_name":"John"}';
4
​
5
​
6
SELECT *
7
FROM directors_docs
8
WHERE body @> '{"director_id":1}';
9
​
10
-- error : No operator matches the given name and argument types. You might need to add explicit type casts.
11
SELECT *
12
FROM directors_docs
13
WHERE body -> 'first_name' LIKE 'J%';
14
​
15
​
16
SELECT *
17
FROM directors_docs
18
WHERE body ->> 'first_name' LIKE 'J%';
19
​
20
SELECT *
21
FROM directors_docs
22
WHERE (body ->> 'director_id')::integer in (1,2,3,4,5,10);
Copied!
Last modified 3mo ago