SQL : JSON Datatype
source link: https://dev.to/dev117uday/sql-json-datatype-11j1
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
This guide is for PostgreSQL : https://www.postgresql.org/
Download the sample data from here : sample_data.sql
JSON vs JSONB
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","[email protected]"}');
json_object
----------------------------------------------
{"name" : "adnan", "email" : "[email protected]"}
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);
Recommend
-
31
Oracle started adding JSON support to Oracle Database with version 12.1.0.2. The earliest support was targeted at storing, indexing, and querying JSON data. Version 12.2 rounded out that support by adding features for ge...
-
4
Cooking Classes with Datatype Generic Programming Haskell Generics are a somewhat misunderstood topic but are an extremely powerful technique for writing reusable and comparable interfaces across an enormous universe of types with ve...
-
7
A Conflict-Free Replicated JSON Datatype Martin Kleppmann and Alastair R. Beresford IEEE Transactions on Parallel and Distributed Systems 28(10):2733–27...
-
8
TL;DR: We need DataFrame as a data structure in Ruby. There are several promising candidates but no one with good usability. Some considerations on requirements to good DataFrame library are proposed, alongside with...
-
4
Just the other day I received an email from a client who was confused about the Datatype Decimal. Let us learn today how it works in this quick blog.First of all, lots of people think that Datatype Decimal and Datatype Numeric are diff...
-
3
A Conflict-Free Replicated JSON DatatypeIEEE websites place cookies on your device to give you the best user experience. By using our websites, you agree to the placement of these cookies. To learn more, read our
-
8
The Three Laws of Robotic Vacuum Cleaners and the ANYDATA Datatype Posted on October 6, 2021
-
3
<?xml encoding="utf-8" ??>Introduction In MySQL, you can use the ENUM data type to specify a list of permitted values in a column. For instance, in a customers table, y...
-
5
Ecma TC39 JavaScript Decimal proposal The TC39 Decimal proposal is an investigation into adding a built-in data type in JavaScript to represent base-10 decimal numbers. This whole proposal is basically a big open question, and we'd...
-
3
Convet blob datatype into varchar in BODS 4.2 Skip to Content D...
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK