json-тип
инструкция
в соответствии сRFC 7159Как объяснялось в разделе , тип данных JSON используется для хранения данных JSON (нотация объектов JavaScript). Эти данные также могут быть сохранены какtext, но тип данных JSON имеет то преимущество, что каждое сохраненное значение соответствует правилам JSON. Существует также множество функций и операторов, связанных с JSON, которые можно использовать с данными, хранящимися в этих типах данных.
PostgreSQL поддерживает два типа данных JSON: json и jsonb. Они принимают на вход почти точно такой же набор значений. Самая большая разница между ними заключается в эффективности. Тип данных json хранит точную копию входного текста, которую обработчик должен повторно анализировать при каждом выполнении. В то время как данные jsonb хранятся в декомпозированном двоичном формате, они немного медленнее при вводе из-за дополнительных преобразований, которые необходимо выполнить. Но jsonb намного быстрее обрабатывается, потому что повторная обработка не требуется.
Важно: jsonb поддерживает индексацию
Поскольку тип json хранит точную копию вводимого текста, он сохраняет пробелы и порядок ключей внутри объекта JSON. Если объект JSON в значении содержит один и тот же ключ более одного раза, все пары ключ/значение сохраняются (**функция обработки рассматривает последнее значение как допустимое значение**).
jsonb не сохраняет пробелы, не сохраняет порядок ключей объектов и не сохраняет повторяющиеся ключи объектов. Если во входных данных указаны повторяющиеся ключи, будет сохранено только последнее значение.
Данные JSON рекомендуется хранить как jsonb
При преобразовании текстового ввода JSON в jsonb базовый тип JSON (RFC 7159 ) будут сопоставлены с собственными типами PostgreSQL. Поэтому данные jsonb имеют некоторые дополнительные ограничения.比如：jsonb будет отклонять числа вне диапазона числового типа данных PostgreSQL, а json — нет.
Примитивные типы JSON и соответствующие типы PostgreSQL
| Основные типы JSON | Тип PostgreSQL | Примечания |
|---|---|---|
string |
text |
не допускается\u0000, если кодировка базы данных не UTF8, то же верно и для escape-последовательностей Unicode, отличных от ASCII. |
number |
numeric |
не допускаетсяNaNиinfinityценность |
boolean |
boolean |
только нижний регистрtrueиfalseзаклинание |
null |
(никто) | SQL NULLэто другое понятие |
Синтаксис ввода и вывода json
-- 简单标量/基本值
-- 基本值可以是数字、带引号的字符串、true、false或者null
SELECT '5'::json;
-- 有零个或者更多元素的数组（元素不需要为同一类型）
SELECT '[1, 2, "foo", null]'::json;
-- 包含键值对的对象
-- 注意对象键必须总是带引号的字符串
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;
-- 数组和对象可以被任意嵌套
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;
-- "->" 通过键获得 JSON 对象域 结果为json对象
select '{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json->'nickname' as nickname;
nickname
-------------
"goodspeed"
-- "->>" 通过键获得 JSON 对象域 结果为text
select '{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json->>'nickname' as nickname;
nickname
-----------
goodspeed
-- "->" 通过键获得 JSON 对象域 结果为json对象
select '{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->'nickname' as nickname;
nickname
-------------
"goodspeed"
-- "->>" 通过键获得 JSON 对象域 结果为text
select '{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->>'nickname' as nickname;
nickname
-----------
goodspeed
Когда значение JSON вводится, а затем выводится без какой-либо дополнительной обработки, json выводит тот же текст, что и ввод, а jsonb не сохраняет семантически бессмысленные детали.
SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;
json
-------------------------------------------------
{"bar": "baz", "balance": 7.77, "active":false}
-- jsonb 不会保留语义上的细节，key 的顺序也和原始数据不一致
SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;
jsonb
--------------------------------------------------
{"bar": "baz", "active": false, "balance": 7.77}
синтаксис json-запроса
При работе с документами JSON рекомендуется хранить документы JSON как фиксированную структуру. (Структура необязательна, но наличие предсказуемой структуры упрощает запросы к коллекции.)
设计JSON文档建议：Любое обновление требует блокировки всей строки на уровне строки. Чтобы уменьшить конкуренцию за блокировку, каждый документ JSON должен представлять атомарные данные (неделимые, независимо изменяемые данные в соответствии с бизнес-правилами).
Эти общие операторы сравнения действительны только для jsonb, но не для json.
Часто используемые операторы сравнения
| оператор | описывать |
|---|---|
< |
меньше, чем |
> |
больше, чем |
<= |
меньше или равно |
>= |
больше или равно |
= |
равный |
<> or !=
|
не равно |
содержать и существовать
запрос данных json (для jsonb)
операторы json и jsonb
-> 和 ->>оператор
Используйте ->>, чтобы узнать данные в виде текста Использовать -> обнаруженные данные являются объектом json
-- nickname 为 gs 的用户 这里使用 ->> 查出的数据为text，所以匹配项也应该是text
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json->>'nickname' = 'gs';
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->>'nickname' = 'gs';
-- 使用 -> 查询，会抛出错误，这里无论匹配项是text类型的 'gs' 还是 json 类型的 '"gs"'::json都会抛出异常，json 类型不支持 等号（=）操作符
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json->'nickname' = '"gs"';
ERROR: operator does not exist: json = unknown
-- json 类型不支持 "=" 操作符
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json->'nickname' = '"gs"'::json;
ERROR: operator does not exist: json = json
-- jsonb 格式是可以查询成功的，这里使用 -> 查出的数据为json 对象，所以匹配项也应该是json 对象
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->'nickname' = '"gs"';
#> 和 #>>оператор
Используйте #>>, чтобы узнать данные в виде текста Используйте #>, чтобы узнать данные как объект json
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json#>'{tags,0}' as tag;
tag
----------
"python"
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json#>>'{tags,0}' as tag;
tag
--------
python
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb#>'{tags,0}' = '"python"';
?column?
----------
t
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb#>>'{tags,0}' = 'python';
?column?
----------
t
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json#>>'{tags,0}' = 'python';
?column?
----------
t
-- 会抛出错误，这里无论匹配项是text类型的 'python' 还是 json 类型的 '"python"'::json都会抛出异常，json 类型不支持 等号（=）操作符
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json#>'{tags,0}' = '"python"';
ERROR: operator does not exist: json = unknown
запрос данных jsonb (не для json)
** Дополнительные операторы jsonb **
@>оператор
-- nickname 为 nickname 的用户
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb @> '{"nickname": "gs"}'::jsonb;
-- 等同于以下查询
-- 这里使用 -> 查出的数据为json 对象，所以匹配项也应该是json 对象
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->'nickname' = '"gs"';
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->>'nickname' = 'gs';
-- 查询有 python 和 golang 标签的数据
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb @> '{"tags": ["python", "golang"]}';
?column?
----------
t
?оператор,?|оператор и?&оператор
-- 查询有 avatar 属性的用户
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb ? 'avatar';
-- 查询有 avatar 属性 并且avatar 数据不为空的数据
select '{"nickname": "gs", "avatar": null, "tags": ["python", "golang", "db"]}'::jsonb->>'avatar' is not null;
-- 查询 有 avatar 或 tags 的数据
select '{"nickname": "gs", "tags": ["python", "golang", "db"]}'::jsonb ?| array['avatar', 'tags'];
?column?
----------
t
-- 查询 既有 avatar 又有 tags 的用户
select '{"nickname": "gs", "tags": ["python", "golang", "db"]}'::jsonb ?& array['avatar', 'tags'];
?column?
----------
f
-- 查询 tags 中包含 python 标签的数据
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->'tags' ? 'python';
?column?
----------
t
json обновление
-- 更新 account content 字段（覆盖式更新）
update account set content = jsonb_set(content, '{}', '{"nickname": "gs", "tags": ["python", "golang", "db"]}', false);
-- 修改nickanme为nickanme 的用户标签
update account set content = jsonb_set(content, '{tags}', '["test", "心理"]', true) where content @> '{"nickname": "nickname"}'::jsonb;
update account set content = jsonb_set(content, '{tags}', '["test", "心理", "医疗"]', true) where content @> '{"nickname": "nickname"}'::jsonb;
-- 更新account content字段中 weixin_mp 的值（如果没有会创建）
update account set content = jsonb_set(content, '{weixin_mp}', '"weixin_mp5522bd28-ed4d-11e8-949c-7200014964f0"', true) where id='5522bd28-ed4d-11e8-949c-7200014964f0';
-- 更新account 去除content 中weixin 字段（如果没有weixin 字段也不会抛出异常）
update account set content= content - 'weixin' where id='5522bd28-ed4d-11e8-949c-7200014964f0';
json-функция
jsonb_pretty
Вернуть from_json как текст JSON с отступом.
select jsonb_pretty('[{"f1":1,"f2":null},2,null,3]');
jsonb_pretty
--------------------
[ +
{ +
"f1": 1, +
"f2": null+
}, +
2, +
null, +
3 +
]
(1 row)
jsonb_set
Параметры функции jsonb_set() следующие:
jsonb_set(target jsonb, // 需要修改的数据
path text[], // 数据路径
new_value jsonb, // 新数据
create_missing boolean default true)
Если create_missing имеет значение true (по умолчанию — true), а путь, указанный путем, не существует в цели, то цель будет содержать часть, указанную путем, часть, замененную новым_значением, или часть, добавленную новым_значением.
-- target 结构
select jsonb_pretty('[{"f1":1,"f2":null},2]');
jsonb_pretty
--------------------
[ +
{ +
"f1": 1, +
"f2": null+
}, +
2 +
]
-- 更新 target 第0 个元素 key 为 f1 的值，如果f1 不存在 忽略
select jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false);
jsonb_set
---------------------------------------------
[{"f1": [2, 3, 4], "f2": null}, 2, null, 3]
-- 更新 target 第0 个元素 key 为 f3 的值，如果f3 不存在 创建
select jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]');
jsonb_set
---------------------------------------------
[{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]
-- 更新 target 第0 个元素 key 为 f3 的值，如果f3 不存在 忽略
select jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]', false);
jsonb_set
---------------------------------------------
[{"f1": 1, "f2": null}, 2]
Подробные сведения о функциях и операторах json см. в документации:JSON-функции и операторы
анализ производительности jsonb
Мы используем следующий пример, чтобы проиллюстрировать производительность запросов json.
Структура таблицы
-- account 表 id 使用uuid 类型，需要先添加uuid-ossp模块。
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- create table
create table account (id UUID NOT NULL PRIMARY KEY default uuid_generate_v1(), content jsonb, created_at timestamptz DEFAULT CURRENT_TIMESTAMP, updated_at timestamptz DEFAULT CURRENT_TIMESTAMP);
json=> \d account
Table "public.account"
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+--------------------
id | uuid | | not null |uuid_generate_v1()
content | jsonb | | |
created_at | timestamp with time zone | | | CURRENT_TIMESTAMP
updated_at | timestamp with time zone | | | CURRENT_TIMESTAMP
Indexes:
"account_pkey" PRIMARY KEY, btree (id)
Хорошей практикой является добавление created_at и updated_at в поле jsonb, вот только пример
Структура данных содержимого:
content = {
"nickname": {"type": "string"},
"avatar": {"type": "string"},
"weixin": {"type": "string"},
"tags": {"type": "array", "items": {"type": "string"}},
}
Подготовить данные
Массовая вставка данных
-- 插入100w条有 nickname avatar tags 为["python", "golang", "c"]的数据
insert into account select uuid_generate_v1(), ('{"nickname": "nn-' || round(random()*20000000) || '", "avatar": "avatar_url", "tags": ["python", "golang", "c"]}')::jsonb from (select * from generate_series(1,100000)) as tmp;
-- 插入100w条有 nickname tags 为["python", "golang"]的数据
insert into account select uuid_generate_v1(), ('{"nickname": "nn-' || round(random()*2000000) || '", "tags": ["python", "golang"]}')::jsonb from (select * from generate_series(1,1000000)) as tmp;
-- 插入100w条有 nickname tags 为["python"]的数据
insert into account select uuid_generate_v1(), ('{"nickname": "nn-' || round(random()*2000000) || '", "tags": ["python"]}')::jsonb from (select * from generate_series(1,1000000)) as tmp;
тестовый запрос
- EXPLAIN: отображает план выполнения, сгенерированный планировщиком PostgreSQL для указанного оператора.
- АНАЛИЗ: сбор статистики о содержимом таблиц в базе данных.
--content 中有avatar key 的数据条数 count(*) 查询不是一个好的测试语句，就算是有索引，也只能起到过滤的作用，如果结果集比较大，查询速度还是会很慢
explain analyze select count(*) from account where content::jsonb ? 'avatar';
QUERY PLAN
----------------------------------------------------------------------------------------
Finalize Aggregate (cost=29280.40..29280.41 rows=1 width=8) (actual time=170.366..170.366 rows=1 loops=1)
-> Gather (cost=29280.19..29280.40 rows=2 width=8) (actual time=170.119..174.451 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=28280.19..28280.20 rows=1 width=8) (actual time=166.034..166.034 rows=1 loops=3)
-> Parallel Seq Scan on account (cost=0.00..28278.83 rows=542 width=0) (actual time=0.022..161.937 rows=33333 loops=3)
Filter: (content ? 'avatar'::text)
Rows Removed by Filter: 400000
Planning Time: 0.048 ms
Execution Time: 174.486 ms
-- content 中没有avatar key 的数据条数
explain analyze select count(*) from account where content::jsonb ? 'avatar' = false;
QUERY PLAN
----------------------------------------------------------------------------------------
Finalize Aggregate (cost=30631.86..30631.87 rows=1 width=8) (actual time=207.770..207.770 rows=1 loops=1)
-> Gather (cost=30631.65..30631.86 rows=2 width=8) (actual time=207.681..212.357 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=29631.65..29631.66 rows=1 width=8) (actual time=203.565..203.565 rows=1 loops=3)
-> Parallel Seq Scan on account (cost=0.00..28278.83 rows=541125 width=0) (actual time=0.050..163.629 rows=400000 loops=3)
Filter: (NOT (content ? 'avatar'::text))
Rows Removed by Filter: 33333
Planning Time: 0.050 ms
Execution Time: 212.393 ms
--查询content 中nickname 为nn-194318的数据
explain analyze select * from account where content@>'{"nickname": "nn-194318"}';
QUERY PLAN
----------------------------------------------------------------------------------------
Gather (cost=1000.00..29408.83 rows=1300 width=100) (actual time=0.159..206.990 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on account (cost=0.00..28278.83 rows=542 width=100) (actual time=130.867..198.081 rows=0 loops=3)
Filter: (content @> '{"nickname": "nn-194318"}'::jsonb)
Rows Removed by Filter: 433333
Planning Time: 0.047 ms
Execution Time: 207.007 ms
-- 对应的查询id 为 'b5b3ed06-7d35-11e9-b3ea-00909e9dab1d' 的数据
explain analyze select * from account where id='b5b3ed06-7d35-11e9-b3ea-00909e9dab1d';
QUERY PLAN
----------------------------------------------------------------------------------------
Index Scan using account_pkey on account (cost=0.43..8.45 rows=1 width=100) (actual time=0.912..0.914 rows=1 loops=1)
Index Cond: (id = 'b5b3ed06-7d35-11e9-b3ea-00909e9dab1d'::uuid)
Planning Time: 0.348 ms
Execution Time: 0.931 ms
Из результатов мы видим, что существует огромная разница в скорости между использованием запроса jsonb и использованием запроса первичного ключа.Глядя на записи анализа запроса, мы видим, что самая большая разница между этими двумя операторами заключается в том, что запрос, использующий первичный ключ ключ использует индекс, в то время как запрос псевдонима контента не имеет индекса для использования. Затем проверьте скорость запроса при использовании индекса.
показатель
Введение в индекс GIN
JSONB чаще всего используетсяGINИндексы, индексы GIN можно использовать для эффективного поиска ключей или пар ключ-значение, которые встречаются в большом количестве документов jsonb (данных).
GIN (Generalized Inverted Index, Обобщенный инвертированный индекс) — структура индекса, в которой хранится набор пар (ключ, список постинга), где ключ — значение ключа, а список постинга — набор позиций, где появился ключ. Например, в ('привет', '14:2 23:4') это означает, что привет появился в двух позициях 14:2 и 23:4. В PG эти позиции на самом деле являются tid кортежа (номер строки, включая идентификатор блока данных (32 бита) и точку элемента данных (16 бит)).
Каждый атрибут в таблице может быть разобран на несколько ключевых значений при индексации, поэтому tid одного и того же кортежа может появиться в списке постинга нескольких ключей.
Благодаря этой структуре индекса можно быстро найти кортежи, содержащие указанные ключевые слова, поэтому индекс GIN особенно подходит для поиска элементов многозначных типов, таких как поддержка полнотекстового поиска, поиск элементов в массивах, и индексный модуль GIN PG изначально также был Разработан для поддержки полнотекстового поиска.
jsonbКласс операторов GIN по умолчанию поддерживает операторы присутствия с ключами верхнего уровня.?,?&а также?|Операторы и операторы существования пути/значения@>запрос.
-- 创建默认索引
CREATE INDEX idxgin ON api USING GIN (jdoc);
Класс оператора GIN не по умолчаниюjsonb_path_opsПоддерживает только индексы@>оператор.
-- 创建指定路径的索引
CREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops);
-- create index ix_account_content_nickname_gin on account using gin (content, (content->'nickname'));
-- create index ix_account_content_tags_gin on account using gin (content, (content->'nickname'));
-- create index ix_account_content_tags_gin on account using gin ((content->'tags'));
Поддержка нескольких индексов
PostgreSQL имеет открытый интерфейс индексирования, который позволяет PG поддерживать очень богатые методы индексирования, такие как btree, hash, gin, gist, sp-gist, brin, bloom, rum, zombodb, bitmap (greenplum extension) и сценарий запроса. , выберите другой индекс.
Оптимизация запросов
Создать индекс по умолчанию
-- 创建简单索引
create index ix_account_content on account USING GIN (content);
Теперь индекс может использоваться запросом, подобным следующему:
-- content 中有avatar key 的数据条数
explain analyze select count(*) from account where content::jsonb ? 'avatar';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4180.49..4180.50 rows=1 width=8) (actual time=43.462..43.462 rows=1 loops=1)
-> Bitmap Heap Scan on account (cost=30.07..4177.24 rows=1300 width=0) (actual time=8.362..36.048 rows=100000 loops=1)
Recheck Cond: (content ? 'avatar'::text)
Heap Blocks: exact=2032
-> Bitmap Index Scan on ix_account_content (cost=0.00..29.75 rows=1300 width=0) (actual time=8.125..8.125 rows=100000 loops=1)
Index Cond: (content ? 'avatar'::text)
Planning Time: 0.078 ms
Execution Time: 43.503 ms
В 3 раза быстрее, чем когда раньше индекс не добавлялся.
-- 查询content 中nickname 为nn-194318的数据
explain analyze select * from account where content@>'{"nickname": "nn-194318"}';
QUERY PLAN
----------------------------------------------------------------------------------------
Bitmap Heap Scan on account (cost=46.08..4193.24 rows=1300 width=100) (actual time=0.097..0.097 rows=1 loops=1)
Recheck Cond: (content @> '{"nickname": "nn-194318"}'::jsonb)
Heap Blocks: exact=1
-> Bitmap Index Scan on ix_account_content (cost=0.00..45.75 rows=1300 width=0) (actual time=0.091..0.091 rows=1 loops=1)
Index Cond: (content @> '{"nickname": "nn-194318"}'::jsonb)
Planning Time: 0.075 ms
Execution Time: 0.132 ms
Эффективность этого запроса более очевидна и даже более эффективна, чем использование первичного ключа.
Но следующий запрос не использует индекс:
-- 查询content 中不存在 avatar key 的数据条数
explain analyze select count(*) from account where content::jsonb ? 'avatar' = false;
QUERY PLAN
----------------------------------------------------------------------------------------
Finalize Aggregate (cost=30631.86..30631.87 rows=1 width=8) (actual time=207.641..207.641 rows=1 loops=1)
-> Gather (cost=30631.65..30631.86 rows=2 width=8) (actual time=207.510..211.062 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=29631.65..29631.66 rows=1 width=8) (actual time=203.739..203.739 rows=1 loops=3)
-> Parallel Seq Scan on account (cost=0.00..28278.83 rows=541125 width=0) (actual time=0.024..163.444 rows=400000 loops=3)
Filter: (NOT (content ? 'avatar'::text))
Rows Removed by Filter: 33333
Planning Time: 0.068 ms
Execution Time: 211.097 ms
Индекс также нельзя использовать для запросов, подобных приведенным ниже, потому что хотя оператор?индексируется, но его нельзя применить непосредственно к содержимому индексированного столбца:
explain analyze select count(1) from account where content -> 'tags' ? 'c';
QUERY PLAN
----------------------------------------------------------------------------------------
Finalize Aggregate (cost=30634.57..30634.58 rows=1 width=8) (actual time=184.864..184.864 rows=1 loops=1)
-> Gather (cost=30634.35..30634.56 rows=2 width=8) (actual time=184.754..189.652 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=29634.35..29634.36 rows=1 width=8) (actual time=180.755..180.755 rows=1 loops=3)
-> Parallel Seq Scan on account (cost=0.00..29633.00 rows=542 width=0) (actual time=0.022..177.051 rows=33333 loops=3)
Filter: ((content -> 'tags'::text) ? 'c'::text)
Rows Removed by Filter: 400000
Planning Time: 0.074 ms
Execution Time: 189.716 ms
индексировать с помощью выражений
-- 创建路径索引
create index ix_account_content_tags on account USING GIN ((content->'tags'));
-- 测试查询性能
explain analyze select count(1) from account where content -> 'tags' ? 'c';
QUERY PLAN
----------------------------------------------------------------------------------------
Aggregate (cost=4631.74..4631.75 rows=1 width=8) (actual time=49.274..49.275 rows=1 loops=1)
-> Bitmap Heap Scan on account (cost=478.07..4628.49 rows=1300 width=0) (actual time=8.655..42.074 rows=100000 loops=1)
Recheck Cond: ((content -> 'tags'::text) ? 'c'::text)
Heap Blocks: exact=2032
-> Bitmap Index Scan on ix_account_content_tags (cost=0.00..477.75 rows=1300 width=0) (actual time=8.417..8.417 rows=100000 loops=1)
Index Cond: ((content -> 'tags'::text) ? 'c'::text)
Planning Time: 0.216 ms
Execution Time: 49.309 ms
Теперь предложение WHEREcontent -> 'tags' ? 'c'будет распознан как индексируемый оператор? в индексном выраженииcontent -> 'tags'приложение включено.
Вы также можете воспользоваться включенными запросами, например:
-- 查寻 "tags" 包含数组元素 "c" 的数据的个数
select count(1) from account where content @> '{"tags": ["c"]}';
Простой индекс GIN для столбца содержимого (индекс по умолчанию) может поддерживать индексированные запросы. Но индекс будетСохраните копию каждого ключа и значения в столбце содержимого.,индекс выраженияСохраняются только данные, найденные под ключом tags.
Хотя метод простого индекса является более гибким (поскольку он поддерживает запросы по произвольным ключам), индексы с направленными выражениями меньше и выполняют поиск быстрее, чем простые индексы. Хотя класс операторов jsonb_path_ops поддерживает только запросы с оператором @>, он имеет более объективные преимущества в производительности, чем класс операторов по умолчанию jsonb_ops. Индекс jsonb_path_ops также обычно намного меньше, чем jsonb_ops для тех же данных, а поиск более конкретен, особенно когда запросы включают ключи, которые часто встречаются в этих данных. Поэтому операции поиска в нем обычно выполняются лучше, чем поиск с использованием класса операторов по умолчанию.
Суммировать
- PG имеет два типа данных JSON:
jsonиjsonb, jsonb имеет лучшую производительность, чем json, и jsonb поддерживает индексирование. - jsonb будет обрабатывать записанные данные при записи, запись относительно медленная, json сохранит исходные данные (включая ненужные пробелы)
- Хороший способ оптимизировать запрос jsonb — добавить индекс GIN.
- Простые индексы более гибкие, чем индексы пути, но занимают больше места.
- Индексы пути более эффективны и занимают меньше места, чем простые индексы.
Ссылка на ссылку
- RFC 7159 The JavaScript Object Notation (JSON) Data Interchange Format
- Документация PostgreSQL: тип JSON
- JSON-функции и операторы
- How do I modify fields inside the new PostgreSQL JSON datatype?
- Принципы и сценарии применения 9 индексов PostgreSQL
- Принцип реализации GIN-индекса PostgreSQL
- PostgreSQL internals: JSONB type and its indexes
- Перевернутый индекс
Наконец, поблагодарите мою девушку за ее поддержку и терпимость, чем ❤️
Вы также можете ввести следующие ключевые слова в официальном аккаунте, чтобы получить исторические статьи:公号&小程序 | 设计模式 | 并发&协程