После 2 постов о jsonb в Postgres, которые содержат информацию о нем… Настало время испачкать руки в jsonb…🔥
Добавить колонку
Для работы с jsonb давайте сначала создадим таблицу и добавим столбец jsonb
в качестве команды базы данных, например
CREATE TABLE cards (
id integer NOT NULL,
board_id integer
data jsonb
);
или в миграции по рельсам
create_table :books do |t|
t.string :title
t.jsonb :payload, null: false
, default: {}
t.timestamps
end
или мы можем просто добавить столбец jsonb к существующей таблице в миграции по рельсам
add_column :users, :data_jsonb, :jsonb
Вставка данных
Для вставки JSON данных в базу данных по строке SQL мы передаем все JSON значение как строку с одинарными кавычками.
INSERT INTO books (title, payload,created_at,updated_at) VALUES ('book12','{"price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}],
"publisher": "publisher1", "published_date": "2017-15-07"}', '2022-15-05 19:55:21.364624', '2022-05-05 19:55:21.364624');
В rails работайте с ним как с хэшем
book_payload = {
publisher: 'Currency',
published_date: '2017-04-07',
authors: [{id: 1,name: 'Sean Ellis'},{id: 2,name: 'Morgan Brown'}]
}
Book.create(title: ""book1", payload: book_payload)"
Показать данные
Показать данные в rails очень просто.
Обратите внимание, что любой столбец, определенный как JSONB, будет представлен в rails как хэш, со всеми ключами, возвращаемыми в виде строки.
> Book.first.payload[:publisher]
=> nil
> Book.first.payload['publisher']
=> "Currency
> Book.last.payload['authors'][0]['name']
=> "Sean Ellis"
Операторы json и jsonb
В этой таблице из PostgreSQL показаны операторы, доступные для использования с двумя типами данных JSON.
Оператор | Тип правого операнда | Описание | Пример | Пример Результат |
---|---|---|---|---|
-> | int | Получение элемента массива JSON (индексируется с нуля, отрицательные целые числа считаются с конца) | ‘[{«a»:»foo»},{«b»:»bar»},{«c»:»baz»}]’::json->2 | {«c»: «baz»} |
-> | текст | Получение поля объекта JSON по ключу | ‘{«a»: {«b»:»foo»}}’::json->’a’ | {«b»: «foo»} |
->> | int | Получение элемента массива JSON в виде текста | ‘[1,2,3]’::json->>2 | 3 |
->> | текст | Получить поле объекта JSON как текст | ‘{«a»:1,»b»:2}’::json->>’b’ | 2 |
#> | text[] | Получить объект JSON по указанному пути | ‘{«a»: {«b»:{«c»: «foo»}}}’::json#>'{a,b}’ | {«c»: «foo»} |
#>> | text[] | Получить объект JSON по указанному пути в виде текста | ‘{«a»:[1,2,3],»b»:[4,5,6]}’::json#>>'{a,2}’ | 3 |
Чтобы запросить данные JSON, постройте оператор SELECT
как
Те может принимать имя ключа для запроса в jsonb или индекс, если запрос в массиве jsonb, так как ключ должен быть текстом с одинарной кавычкой, а индекс должен быть целым числом
Хорошо, но когда теперь использовать ->
и ->>
?
Используйте ->
, когда вы хотите вернуть значение как оно есть с его типом и ->>
, когда вы хотите получить значение как строку (тип текста позы), например:
используйте оператор ->
, чтобы продолжать возвращать объекты в виде цепочки, пока мы не достигнем конечного атрибута, который нам нужен, где можно использовать ->>
для возврата в виде текста.
SELECT pg_typeof(payload -> 'authors' ->> 1) AS author FROM "books" Where id = 20;
# author
# --------
# text
# (1 row)
Посмотрите здесь, чтобы получить значение индекса 1 из authors, мы хотим получить authors как массив, чтобы иметь возможность искать индекс внутри него, но для значения индекса 1 мы должны вернуть его как текст, так как это нормально, потому что мы не заботились о его типе.
Не используйте кавычки (‘) с именем колонки. В то время как атрибуты JSON всегда используют кавычки (‘).
то же самое для #>
и #>>
, но здесь они будут принимать массив текста за путь, чтобы получить значение ключа или элемента в массиве jsonb
примеры
В rails
=> Book.select("payload -> 'authors' -> 1 AS author").map(&:author)
=> [nil, {"id"=>2, "name"=>"Morgan Brown"}, {"id"=>3, "name"=>"Morgan Browns"}]
>Book.select("payload -> 'authors' ->> 1 AS author").map(&:author)
=> [nil, "{"id": 2, "name": "Morgan Brown"}", "{"id": 3, "name": "Morgan Browns"}"]?
>Book.select("payload ->> 'publisher' AS publisher").map(&:publisher)
=> [nil, "Currency", "Currency2"]
# it is like Book.select("payload -> 'authors' -> 1 AS author").map(&:author)
Book.select("payload #> '{authors, 1}' AS author").map(&:author)
=> [nil, {"id"=>2, "name"=>"Morgan Brown"}, {"id"=>3, "name"=>"Morgan Browns"}]
Book.select("payload #>> '{authors, 1,name}' AS author_name").map(&:author_name)
=> [nil, "Morgan Brown", "Morgan Browns"]
проверить тип возврата ->
и ->>
в Postgres
SELECT jsonb_typeof(payload -> 'authors' -> 1) AS author FROM "books" Where id = 20;
# author
# --------
# object
# (1 row)
SELECT pg_typeof(payload -> 'authors' ->> 1) AS author FROM "books" Where id = 20;
# author
# --------
# text
# (1 row)
или использовать их с равенством для фильтрации данных …
Примечание: у нас не было ключа 0, так как индексы должны быть целыми.
Book.where("payload -> 'authors' -> '0' ->> 'name' = :name", name: 'author1')
# => []
Book.where("payload -> 'authors' -> 0 ->> 'name' = :name", name: 'author1')
# Book Load (0.6ms) SELECT "books".* FROM "books" WHERE (payload -> 'authors' -> 0 ->> 'name' = 'author1')
# =>
# [#<Book:0x000055b6f13b8528
# id: 22,
# title: "book3",
# payload:
# {"price"=>170, "authors"=>[{"id"=>1, "name"=>"author1"}, {"id"=>3, "name"=>"author3"}], "publisher"=>"publisher2", "published_date"=>"2018-04-07"},
# created_at: Sat, 07 May 2022 18:44:33.087149000 UTC +00:00,
# updated_at: Sat, 07 May 2022 18:44:33.087149000 UTC +00:00>,
# #<Book:0x000055b6f13b8438
# id: 20,
# title: "book1",
# payload:
# {"tags"=>["tag3"],
# "price"=>100,
# "authors"=>[{"id"=>1, "name"=>"author1"}, {"id"=>2, "name"=>"author2"}],
# "publisher"=>"new publisher",
# "published_date"=>"2017-04-07"},
# created_at: Sat, 07 May 2022 18:44:33.055553000 UTC +00:00,
# updated_at: Sat, 07 May 2022 18:44:33.055553000 UTC +00:00>]
то же самое, что
Book.where("payload #>> '{authors,0,name}' = :name", name: 'author1')
Book.where("payload -> 'authors' -> 0 @> :val", val: { name: 'author1' }.to_json)
или можно сказать, что возвращает книгу, которая имеет пустую полезную нагрузку
SELECT "books".* FROM "books" WHERE (payload = '{}');
# id | title | payload | created_at | updated_at
# ----+-------+---------+----------------------------+----------------------------
# 35 | book5 | {} | 2022-06-11 18:30:03.332863 | 2022-06-11 18:30:03.332863
# (1 row)
Book.where("payload = :val", val: {}.to_json)
# Book Load (0.4ms) SELECT "books".* FROM "books" WHERE (payload = '{}')
# =>
# [#<Book:0x000055b6eec4f428
# id: 35,
# title: "book5",
# payload: {},
# created_at: Sat, 11 Jun 2022 18:30:03.332863000 UTC +00:00,
# updated_at: Sat, 11 Jun 2022 18:30:03.332863000 UTC +00:00>]
remember to use to_json to keep the same hash converted to the correct JSON data type
Book.where("payload = :val", val: {})
# Book Load (0.4ms) SELECT "books".* FROM "books" WHERE (payload = NULL)
# => []
посмотрите на запрос WHERE (payload = NULL)
, так как здесь нам нужен пустой {}, а не Null
.
Дополнительные операторы только для jsonb
Некоторые дополнительные операторы также существуют только для jsonb, как показано в этой таблице от PostgreSQL
Многие из этих операторов могут быть проиндексированы классами операторов jsonb:
Оператор | Тип правого операнда | Описание | Пример |
---|---|---|---|
@> | jsonb | Содержит ли левое значение JSON внутри себя правое значение? | ‘{«a»:1, «b»:2}’::jsonb @> ‘{«b»:2}’::jsonb |
<@ | jsonb | Содержится ли левое JSON-значение в правом значении? | ‘{«b»:2}’::jsonb <@ ‘{«a»:1, «b»:2}’::jsonb |
? | текст | Существует ли строка ключа/элемента в JSON-значении? | ‘{«a»:1, «b»:2}’::jsonb ? ‘b’ |
? | text[] | Существуют ли какие-либо из этих строк ключей/элементов. | |
‘& | text[] | Все ли из этих строк ключей/элементов существуют? | ‘[«a», «b»]’::jsonb ?& array[‘a’, ‘b’]. |
примеры :
@> и <@ :
SELECT '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb ;
# ?column?
# ----------
# t
# (1 row)
SELECT '{"a":1, "b":2}'::jsonb @> '{"b":3}'::jsonb ;
# ?column?
# ----------
# f
# (1 row)
является ли все значение в {«b»:2, «a»:5} частью {«a»:1, «b»:2} или частью {«a»:1, «b»:2}
SELECT '{"a":1, "b":2}'::jsonb @> '{"b":2,"a":5}'::jsonb ;
# ?column?
# ----------
# f
# (1 row)
в рельсах
Book.where("? <@ payload", { publisher: nil }.to_json)
# Book Load (0.5ms) SELECT "books".* FROM "books" WHERE ('{"publisher":null}' <@ payload)
# =>
# id: 1,
# title: "book1",
# payload: {"publisher"=>nil},
# updated_at: Tue, 05 Apr 2022 23:20:11.788982000 UTC +00:00>]
?,?|, ?&:
здесь проверяется, содержит ли jsonb ключ или массив jsonb содержит элемент, так как элемент должен быть текстом …
Проверьте эти примеры, чтобы понять это
Существует ли ключ d
в массиве {"a":1, "b":2, "c":3}
?
SELECT '{"a":1, "b":2, "c":3}'::jsonb ? 'd';
# ?column?
# ----------
# f
# (1 row)
SELECT '{"a":1, "b":2, "c":3}'::jsonb ? 'a';
# ?column?
# ----------
# t
# (1 row)
проверить, находится ли элемент внутри массива jsonb …
Существует ли элемент a
внутри массива jsonb ["a", "b"]
?
SELECT '["a", "b"]'::jsonb ? 'a';
# ?column?
# ----------
# t
# (1 row)
SELECT '["a", "b"]'::jsonb ? 'c';
# ?column?
# ----------
# f
# (1 row)
Существуют ли ключи b
или c
в массиве {"a":1, "b":2, "c":3}
? Да, оба существуют
SELECT '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c'];
# ?column?
# ----------
# t
# (1 row)
Существуют ли клавиши a
или d
в пределах {"a":1, "b":2, "c":3}
?
Да a
существует
SELECT '{"a":1, "b":2, "c":3}'::jsonb ?| array['a', 'd'];
# ?column?
# ----------
# t
# (1 row)
Существуют ли клавиши f
или d
в пределах {"a":1, "b":2, "c":3}
? Нет обоих нет
SELECT '{"a":1, "b":2, "c":3}'::jsonb ?| array['f', 'd'];
# ?column?
# ----------
# f
# (1 row)
то же самое для элементов в массиве
SELECT '["a", "b"]'::jsonb ?| array['a', 'b'];
# ?column?
# ----------
# t
# (1 row)
SELECT '["a", "b"]'::jsonb ?| array['a', 'c'];
# ?column?
# ----------
# t
# (1 row)
SELECT '["a", "b"]'::jsonb ?| array['c', 'f'];
# ?column?
# ----------
# f
# (1 row)
Существуют ли ключи b
и c
в пределах {"a":1, "b":2, "c":3}
? Да, оба существуют
SELECT '{"a":1, "b":2, "c":3}'::jsonb ?& array['b', 'c'];
# ?column?
# ----------
# t
# (1 row)
Существуют ли клавиши b
и f
в пределах {"a":1, "b":2, "c":3}
? НЕТ f
не существует
SELECT '{"a":1, "b":2, "c":3}'::jsonb ?& array['b', 'f'];
# ?column?
# ----------
# f
# (1 row)
Существуют ли клавиши f
и d
в пределах {"a":1, "b":2, "c":3}
? Нет, оба не существуют
SELECT '{"a":1, "b":2, "c":3}'::jsonb ?& array['d', 'f'];
# ?column?
# ----------
# f
# (1 row)
то же самое для элементов в массиве
SELECT '["a", "b"]'::jsonb ?& array['a', 'b'];
# ?column?
# ----------
# t
# (1 row)
SELECT '["a", "b"]'::jsonb ?& array['a', 'c'];
# ?column?
# ----------
# f
# (1 row)
SELECT '["a", "b"]'::jsonb ?& array['d', 'c'];
# ?column?
# ----------
# f
# (1 row)
Проверка клавиш в направляющих
Book.where("payload ? :key", key: 'authors').count
# (0.5ms) SELECT COUNT(*) FROM "books" WHERE (payload ? 'authors')
# => 2
Book.where("payload ?| array[:keys]", keys: ['authors','publisher']).count
# (0.7ms) SELECT COUNT(*) FROM "books" WHERE (payload ?| array['authors','publisher'])
# => 3
Book.where("payload ?& array[:keys]", keys: ['authors','publisher']).count
# (0.6ms) SELECT COUNT(*) FROM "books" WHERE (payload ?&array['authors','publisher'])
# => 2
Если вы хотите искать integer/float, вам нужно добавить явные приведения типов, такие как ::int
и ::float
:
Book.where("(payload #>> '{authors,0,id}')::int = :val", val: 1)
City.where("(payload -> 'geolocation' ->> 'latitude')::float =
:val", val: 48.856613)
С помощью агрегатных функций
Вы также можете использовать группировку по
SELECT
MIN (CAST (payload ->> 'price' AS INTEGER)),
MAX (CAST (payload ->> 'price' AS INTEGER)),
SUM (CAST (payload ->> 'price' AS INTEGER)),
AVG (CAST (payload ->> 'price' AS INTEGER))
FROM books;
--this will give us
-- min | max | sum | avg
-------+-----+-----+----------------------
--100 | 200 | 470 | 156.6666666666666667
Другие примеры в SQL
SELECT
MIN (CAST (payload ->> 'price' AS INTEGER)),
MAX (CAST (payload ->> 'price' AS INTEGER)),
SUM (CAST (payload ->> 'price' AS INTEGER)),
AVG (CAST (payload ->> 'price' AS INTEGER))
FROM books
GROUP BY payload ->> 'publisher';
-- the first row is null
-- min | max | sum | avg
-------+-----+-----+----------------------
-- | | |
-- 100 | 100 | 100 | 100.0000000000000000
-- 100 | 100 | 100 | 100.0000000000000000
-- 170 | 200 | 740 | 185.0000000000000000
-- (4 rows)
Также более продвинутые примеры в rails
Book.minimum("CAST (payload ->> 'price' AS INTEGER)")
# (0.6ms) SELECT MIN(CAST (payload ->> 'price' AS INTEGER)) FROM "books"
# => 100
Book.group("payload ->> 'publisher'").maximum("CAST (payload ->> 'price' AS INTEGER)")
# (0.6ms) SELECT MAX(CAST (payload ->> 'price' AS INTEGER)) AS maximum_cast_payload_price_as_integer, payload ->> 'publisher' AS payload_publisher FROM "books" GROUP BY payload ->> 'publisher'
# => {nil=>nil, "publisher1"=>100, "publisher2"=>200}
Book.group("payload ->> 'publisher'").sum("CAST (payload ->> 'price' AS INTEGER)")
# (0.8ms) SELECT SUM(CAST (payload ->> 'price' AS INTEGER)) AS sum_cast_payload_price_as_integer, payload ->> 'publisher' AS payload_publisher FROM "books" GROUP BY payload ->> 'publisher'
# => {nil=>0, "publisher1"=>100, "publisher2"=>370}
Book.group("payload ->> 'publisher'").average("CAST (payload ->> 'price' AS INTEGER)")
# (0.5ms) SELECT AVG(CAST (payload ->> 'price' AS INTEGER)) AS average_cast_payload_price_as_integer, payload ->> 'publisher' AS payload_publisher FROM "books" GROUP BY payload ->> 'publisher'
# => {nil=>nil, "publisher1"=>0.1e3, "publisher2"=>0.185e3}
Фрагменты кода :
все примеры кода в серии jsonb в моем аккаунте на GitHub 💻jsonb_posts
Ссылки:
https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-json/
https://dev.to/kputra/rails-postgresql-jsonb-part-1-4ibg