Главная » JS

Вставка и запрос jsonb в rails и postgres

На чтение 14 мин. Просмотров 65 Опубликовано

После 2 постов о jsonb в Postgres, которые содержат информацию о нем… Настало время испачкать руки в jsonb…🔥

Содержание
  1. Добавить колонку
  2. Вставка данных
  3. Показать данные
  4. Операторы json и jsonb
  5. примеры
  6. Дополнительные операторы только для jsonb
  7. @> и <@ :
  8. ?,?|, ?&:
  9. С помощью агрегатных функций
  10. Фрагменты кода :
  11. Ссылки:

Добавить колонку

Для работы с 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

Оцените статью
devanswers.ru
Добавить комментарий