JSON в PostgreSQL

Поддержка файлов JSON впервые была представлена ​​в PostgreSQL v9.2, и с каждой новой версией вносятся постоянные улучшения.

Что такое JSON?

JSON означает нотацию объектов JavaScript. Это распространенный способ хранения данных, особенно в веб-приложениях. Он очень похож на HTML или XML и был создан для того, чтобы приложения могли легко читать файлы JSON.

Пары ключ-значение: данные JSON записываются в парах ключ-значение, заключенных в кавычки. Вот пример пары ключ-значение "email": "jsonlearning@gmail.com". «email» здесь является ключом, а « jsonlearning@gmail.com » представляет значение. Эти два разделяются двоеточием «:».

Объекты. Объект представляет собой пару ключ-значение или пары, заключенные в фигурные скобки. Всякий раз, когда пара ключ-значение заключена в фигурные скобки, она становится объектом и может рассматриваться как единое целое. В объект можно добавить несколько пар ключ-значение, разделив их запятой.

Пример объекта JSON:

{
  “email” : “jsonlearning@gmail.com”, 
  “country” : “Russia”
 }

Массивы. Массивы в JSON — это способ хранения коллекции значений в одном объекте JSON. Массив в формате JSON представлен квадратными скобками, [ ] содержащими список значений, разделенных запятыми.

Вот пример массива в формате JSON: [ "apple", "banana", "cherry"].

Массивы в JSON также могут быть вложенными, то есть массив может содержать другие массивы или объекты в качестве значений. Вот пример вложенного массива:

{ "firstname" : "Claire", 
"location" : "United Kingdom", 
"blog" : [{ "id" : "1", 
"title" : "Welcome to my blog" }, 
{ "id" : "2", 
"title" : "My first programming language" }]}

В этом примере вложенных массивов вы можете видеть, что «блог» содержится в массиве, и массив также содержит несколько объектов.

JSONB в PostgreSQL

Что такое тип данных JSONB? И чем он отличается от JSON?

JSONB (JSON Binary) — это тип данных в PostgreSQL, который позволяет хранить данные JSON и манипулировать ими более эффективно и результативно, чем обычный тип данных JSON.

JSONB хранит данные JSON в двоичном формате, что обеспечивает более быструю индексацию и производительность запросов по сравнению с обычным типом данных JSON. Это связано с тем, что двоичный формат обеспечивает более эффективное хранение и извлечение данных JSON, особенно при работе с большими или сложными объектами JSON.

Кроме того, JSONB поддерживает дополнительные параметры индексации, такие как возможность индексировать определенные ключи внутри объекта JSON, что позволяет выполнять запросы еще быстрее.

Обычный тип данных JSON в PostgreSQL хранит данные JSON в виде обычного текста без какого-либо двоичного кодирования или специальной поддержки индексации. Это упрощает использование, но может привести к снижению производительности запросов при работе с большими или сложными объектами JSON.

Как создать таблицу с типом данных JSONB

Вы можете создать таблицу и присвоить столбцу тип данных JSON или JSONB точно так же, как вы присваиваете столбцу тип данных Int, VARCHAR или Double. Вы можете просто присвоить столбцу тип данных JSON или JSONB.

Вот пример создания табличного журнала и присвоения столбцу «diary_information» типа данных JSONB.

CREATE TABLE journal (
  id Int NOT NULL PRIMARY KEY, day VARCHAR, 
  diary_information JSONB
);

Поскольку мы указали тип данных JSONB, любые данные, хранящиеся в этом столбце, должны быть допустимым JSON.

Как вставить данные JSON в таблицы

После создания таблицы и присвоения нашему столбцу типа данных JSONB, как нам вставить значения в столбец? Помните, что данные должны быть в допустимом формате JSON.

Чтобы вставить данные в нашу таблицу, мы используем этот оператор:

INSERT INTO journal (id, day, diary_information) 
VALUES 
  (
    1, “Tuesday”, '{"title": "My first day at work", "Feeling": "Mixed                   feeling"}'
  );

Если мы попытаемся получить информацию с помощью оператора select, SELECT * FROM journal мы получим следующий вывод, означающий, что записи были вставлены.

В следующем разделе мы рассмотрим некоторые функции и операторы.

Обзор функций и операторов JSON

Функции и операторы позволяют хранить, манипулировать и запрашивать данные в формате JSON в PostgreSQL.

Вот некоторые часто используемые функции и операторы PostgreSQL, используемые при работе с файлами JSON:

• ->: этот оператор позволяет вам извлечь определенное значение из объекта JSON, вы указываете ключ как «дочерний» по отношению к «родительскому».

Например:

Чтобы получить определенное значение из объекта JSON с помощью -> оператора, используйте его в инструкции SELECT, как показано ниже:

SELECT 
  Id, 
  day, 
  diary_information -> 'Feeling' AS Feeling 
FROM 
  journal;

Здесь следует отметить, что этот оператор извлекает имя поля с кавычками вокруг него.

• ->>: этот оператор позволяет извлечь поле объекта JSON в виде текста без кавычек из объекта JSON.

Например:

SELECT 
  id, 
  day, 
  dairy_information ->> 'Feeling' as Feeling 
FROM 
  products;

Это позволит извлечь значение ключа «material» в виде текста из столбца «features» в таблице «products».

• json_agg: эта функция объединяет значения JSON в массив JSON.

Например, SELECT json_agg(my_column) FROM my_table; вернет массив JSON, содержащий значения в столбце «my_column» таблицы «my_table».

• jsonb_set: эта функция обновляет поле объекта JSON новым значением. Например:

UPDATE 
  my_table 
SET 
  json_column = jsonb_set(
    json_column, '{field_name}', '"new_value"'
  ) 
WHERE 
  id = 1;

Чтобы обновить существующую запись JSON, мы используем функцию jsonb_set() () в операторе обновления.

Например, чтобы обновить запись в таблице, которую мы создали ранее, вы можете запустить следующий код:

UPDATE 
  journal 
SET 
  diary_information = jsonb_set(
    diary_information, '{Feeling}', '"Excited"'
  ) 
WHERE 
  id = 1;

Это обновит ключ «Feeling» в столбце «diary_information» таблицы «journal» новым значением «Excited».

• JSONB_BUILD_OBJECT: Вставка значений JSON вручную может привести к ошибкам, особенно если вы впервые работаете с данными JSON. Но с помощью этой функции вы можете вводить значения, не беспокоясь о фигурных скобках, двоеточиях и всем остальном.

Вы можете использовать JSONB_BUILD_OBJECT функцию для вставки простой текстовой записи, которая преобразует ее в данные JSON. Например, если вы запустите код:

JSONB_BUILD_OBJECT('Morning', 'Everybody is annoying today', 'Evening', 'Cannot wait to go home’)

Это создаст значение, которое выглядит следующим образом:

{“Morning”: “Everybody is annoying today”, “Evening”: “Cannot wait to go home”} 

Использование этой функции в инструкции вставки:

INSERT INTO journal (id, day, feeling) 
VALUES 
  (
    2, 
    'Wednesday', 
    JSONB_BUILD_OBJECT(
      'Tired', 
      'Everybody is annoying today', 
      'Hungry', 
      'Cannot wait to go home’));

Новая запись будет добавлена ​​в таблицу, и поскольку мы использовали эту JSONB_BUILD__OBJECT функцию, последующие значения будут в формате JSON.

Это те немногие функции и операторы, которые мы можем рассмотреть в этой статье. Подробнее о функциях и операторах JSON в PostgreSQL можно прочитать в официальной документации здесь .

Как работать с массивами JSON в PostgreSQL

В PostgreSQL вы можете хранить данные JSON как значение столбца в таблице, а также использовать массивы JSON для хранения коллекции объектов JSON в одном столбце.

Работа с массивами JSON в PostgreSQL включает в себя различные операции, такие как вставка, запрос и манипулирование данными JSON. Давайте посмотрим, как они работают.

Как вставлять массивы JSON в таблицы

Чтобы вставить массивы JSON в таблицу в PostgreSQL, вы можете использовать оператор INSERT INTO вместе с предложением VALUES, чтобы указать массив JSON как строковое значение.

Вот пример:

Предположим, у вас есть таблица «Сотрудники» со столбцами «id», «имя» и «навыки». Столбец навыков хранит массив объектов JSON, представляющих навыки каждого сотрудника.

Чтобы вставить новую запись о сотруднике со следующими данными:

• id: 1
• name: John
• skills: [{"name": "Python", "level": "Intermediate"}, {"name": "JavaScript", "level": "Expert"}]

Вы можете использовать следующий оператор SQL:

INSERT INTO employees (id, name, skills) 
VALUES 
  (
    1, 'John', '[{"name": "Python", "level": "Intermediate"}, {"name":   "JavaScript", "level": "Expert"}]'
  );

Как запросить массивы JSON с помощью операторов JSON

Для запроса массивов JSON в PostgreSQL вы можете использовать различные функции и операторы JSON, предоставляемые PostgreSQL. Эти функции позволяют извлекать определенные значения или элементы из массива JSON и выполнять над ними различные операции. Давайте посмотрим на пример.

Как извлечь значения из массива JSON

Предположим, у вас есть таблица сотрудников со столбцом навыков, в котором хранится массив объектов JSON, представляющих навыки каждого сотрудника.

Чтобы извлечь имена всех сотрудников, у которых «Python» является одним из навыков, вы можете использовать оператор ->> для извлечения свойства «name» каждого объекта навыка и @> оператор для проверки, содержит ли результирующий массив значение «Python». :

SELECT 
  name 
FROM 
  employees 
WHERE 
  skills @ > '[{"name": "Python"}]' :: jsonb

Это всего лишь пример множества способов запроса массивов JSON и манипулирования ими с помощью операторов JSON, предоставляемых PostgreSQL.

Заключение

В заключение отметим, что поддержка JSON в PostgreSQL дает разработчикам возможность упрощать модели данных, повышать производительность приложений и многое другое. Это также обеспечивает плавную связь между реляционными и нереляционными структурами данных.

Вы узнали о типах данных JSON и JSONB, а также о том, какие пары «ключ-значение», объекты и массивы содержатся в JSON. Вы также узнали о некоторых операторах и функциях PostgreSQL для запроса данных в формате JSON.