Обновление JSON в SQLite с помощью JSON1

Расширение SQLite JSON1 обладает некоторыми действительно полезными возможностями. Однако я не смог выяснить, как я могу обновить или вставить отдельные значения атрибутов JSON.

Вот пример

CREATE TABLE keywords
(
 id INTEGER PRIMARY KEY,
 lang INTEGER NOT NULL,
 kwd TEXT NOT NULL,
 locs TEXT NOT NULL DEFAULT '{}'
);

CREATE INDEX  kwd ON keywords(lang,kwd);

Я использую эту таблицу для хранения поисковых запросов по ключевым словам и записи местоположений, с которых поиск был инициирован в объекте. locs, Пример записи в этой таблице базы данных будет выглядеть так, как показано ниже

 id:1,lang:1,kwd:'stackru',locs:'{"1":1,"2":1,"5":1}'

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

Теперь представьте следующие сценарии

  • Поиск по stackru инициируется из индекса местоположения "2". В этом случае я просто хочу увеличить значение по этому индексу, чтобы после операции читалась соответствующая строка

    id:1,lang:1,kwd:'stackru', locs: '{"1": 1, "2":2, "5": 1}'

  • Поиск по stackru инициируется из ранее неизвестного индекса местоположения "7", и в этом случае соответствующая строка после обновления должна будет считаться

    id:1,lang:1,kwd:'stackru', locs: '{"1": 1, "2": 1, "5": 1,"7": 1}'

Мне не ясно, что на самом деле это можно сделать. Я попробовал что-то вроде

UPDATE keywords json_set(locs,'$.2','2') WHERE kwd = 'stackru';

который дал сообщение об ошибке error near json_set, Я был бы очень признателен всем, кто мог бы рассказать мне, как / следует ли это / можно сделать.

Источник

2 ответа

Решение

Я мог бы просто удалить этот вопрос, но, учитывая, что расширение SQLite JSON1, по-видимому, недостаточно изучено, я чувствовал, что было бы более полезно дать ответ здесь для блага других. То, что я намеревался сделать здесь, возможно, но синтаксис SQL более запутанный.

 UPDATE keywords set  locs = 
 (select json_set(json(keywords.locs),'$.**N**', 
 ifnull(
 (select json_extract(keywords.locs,'$.**N**') from keywords where id = '1'),
 0) 
 + 1) 
 from keywords where id = '1') 
 where id = '1';

выполнит оба обновления, которые я описал в моем первоначальном вопросе выше. Учитывая, как сложно это выглядит, несколько объяснений в порядке

  • UPDATE keywords часть выполняет актуальное обновление, но для этого нужно знать, что обновлять
  • SELECT json_set часть, где мы устанавливаем значение для обновления
  • Если релевантное значение не существует в первую очередь, мы не хотим делать + 1 на нулевое значение, поэтому мы делаем IFNULL ТЕСТОВОЕ ЗАДАНИЕ
  • WHERE id = биты гарантируют, что мы нацелены на правильный ряд

Теперь, поработав некоторое время с JSON1 в SQLite, я хочу поделиться с другими тем же путем. Легко напрасно тратить время на написание чрезвычайно запутанного и сложного в поддержке SQL-кода, пытаясь выполнить манипулирование JSON на месте. Рассмотрите возможность использования SQLite в таблицах памяти - CREATE TEMP TABLE... хранить промежуточные результаты и вместо этого написать последовательность операторов SQL. Это делает код более понятным и понятным.

Источник

Для этого не обязательно создавать такой сложный SQL с подзапросами.

Приведенный ниже SQL удовлетворит ваши потребности.

      UPDATE keywords 
SET locs = json_set(locs,'$.7', IFNULL(json_extract(locs, '$.7'), 0) + 1) 
WHERE kwd = 'stackoverflow';

Я знаю, что это устарело, но это как первая ссылка при поиске, она заслуживает лучшего решения.

Источник
Другие вопросы по тегам