Есть способ с помощью Mysqli одним махом засунуть JSON в базу?

Question

[Tyusha]

Извините за нубский вопрос, но я не программистка.

Получаю от клиента JSON, где ключи в точности совпадают с названиями полей таблицы. Если ли волшебная таблетка, чтобы одним махом сделать INSERT или UPDATE в базу?

Я понимаю, что можно расписать все поля в prepare(), потом забиндить данные каждого поля — это я всё могу... Но может быть есть в mysqli способ, чтобы вжух, и готово!

Comments

[Данил Бабаков]

$array = json_decode(Ваш json);
foreach($array as $key => $value) {
    ...
}

$key - ключ, $value - значение.

[Tyusha]

Не, это медленно и грустно, а хочется ЧУДА, чтобы не разбирать все поля самой.

[Akina]

Простого способа нет.

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

хочется ЧУДА

Поздно - Новый Год уже, дед Мороз тю-тю...

[Tyusha]

Всем спасибо! Всё понятно. Значит чудес не бывает. (

[Дмитрий]

Tyusha, если mysql 8 - то json_table + insert select?

[Максим Припадчев]

Для моего личного просветления а в чем проблема предварительно json конвертировать в csv? ну а потом обычным

COPY table
FROM filepath
WITH (FORMAT CSV, HEADER)

Ох извиниясь, этот код для PostgreSQL которой я собственно пользуюсь, но суть от этого не меняется загрузить данные из csv файла элементарно.

[v3shin]

Tyusha, ну так напишите функцию, составляющую правильный запрос и наслаждайтесь чудом. Будет что-то типа:
$id = insertJsonData($json);

[Akina]

Максим Припадчев, наверное, в том, что COPY - вот ни разу не от MySQL приблуда.. а так-то CSV и в MySQL запросто читается с помощью LOAD DATA - но зачем лишнее преобразование?

[Максим Припадчев]

Akina, Все верно, нужно внимательно вопрос читать, я по запарке о PostgreSQL подумал. Но смыcл не в этом я про то что почему не конвертировать в сsv/

[Akina]

Максим Припадчев,

я про то что почему не конвертировать в сsv

Наверное, потому, что "от клиента получен JSON". А MySQL вполне в состоянии принять JSON, распарсить его и положить в таблицу.

Другой вопрос, что автору не хочется один раз поработать руками. Проще надеяться на то, что найдётся программа с единственной кнопкой "Сделать песдато".

[ThunderCat]

Данил Бабаков, $array = json_decode(Ваш json, true);

[Максим Припадчев]

Akina, Что же спасибо за ваш комментарий, мне было интересно узнать мнения. Никаких сомнений что задача решаема на SQL у меня нет, автор вопроса искал быстрое решение. Например pandas библиотека python конвертирует json в csv в простейшем случае ровно в две очень короткие строчки кода. Ну а заносить данные из csv в таблицу это милое дело. Спасибо за ответ.

[Akina]

Максим Припадчев,

Импорт CSV в таблицу - он тоже требует запроса, причём с указанием всех полей поштучно. Если при конвертации JSON в CSV ещё как-то можно обеспечить детерминированный порядок полей в выходном файле, то вот ориентироваться на соответствие порядку полей в структуре таблицы - занятие крайне опасное. Самое простое, что может быть - изменение структуры таблицы в очередной версии, когда факт существования "дефолтного" экспорта подзабылся. И хорошо, если при этом просто перестанет работать - а если случайно импортируется, но не туда, потому что два поля поменялись местами? потом откатываться по данным всегда неудобно, и даже не всегда возможно. А ещё полученные от клиентов данные порой имеют неполный набор атрибутов, что тоже при неаккуратности может аукнуться. "Быстро, дёшево, качественно - выбери два из трёх" - очень актуальная штука.

Answer 1

[ThunderCat]

Что-то вроде этого?

function mysqli_json_insert($mysqli, $tablename, $json){
  $arr =  json_decode($json, true);
  $fields = '`' . implode('`, `', array_keys($arr)) . '`' ;
  $values = implode(',', array_fill(0, count($arr)));
  $stmt = $mysqli->prepare("insert into `$tablename` ($fields) values ($values)");
  $types = str_repeat('s', count($arr));
  $stmt->bind_param($types, ...$arr);
  $stmt->execute();
}

Писал с руки, так что могут быть очепятки...

Comments

[Tyusha]

Спасибо большое. Надеялась, что есть стандартное средство.

[ThunderCat]

Tyusha, ну, если бы на каждый чих было "стандартное средство", документация бы занимала больше чем бд гугла...

Answer 2

[Adamos]

Если использовать не голый Mysqli, а обертку для него вроде phpfaq.ru/safemysql - задача таки решается в одну строчку:

$db->query('INSERT INTO `table` SET ?u', json_decode($json, true));

Answer 3

[mayton2019]

Варианты. Можно преобразовать JSON в JSON-Lines (где каждая строчка - это независимый JSON документ представляющий строку таблицы).

Потом загрузить это в spark dataframe

Using Scala version 2.12.15 (OpenJDK 64-Bit Server VM, Java 11.0.17)
Type in expressions to have them evaluated.
Type :help for more information.

scala> val df1 = spark.read.json("/tmp/emp.json")
df1: org.apache.spark.sql.DataFrame = [_corrupt_record: string]

И имея фрейм либо сохранить в CSV либо подключить драйвер JDBC записать прямо в базу.

Еще во фреймворке Pandas есть опции загрузки из JSON. Но я сам не спец в пандасе поэтому
точно как сделать не скажу.

Вот. Еще я помню где-то писал утилиту чтоб произвольные JSON конвертить в JSON-lines.

Короче способов - масса. Беда как обычно в вопрошающем. Что он готов для этого установить
и что выучить.

Answer 4

[PigData]

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

1. Просто хранить Json построчно в поле с типом json

2. Если надо раскидывать json по полям, можно создать поля таблицы которые будут автоматически записываться из поля json, примерно так

`firstname`  VARCHAR(255) GENERATED ALWAYS AS (`json` ->> '$.firstname') STORED NULL,

3. Можно просто сконвертить JSON в CSV (5-6 строчек кода) и вставить стандартными средствами. На больших файлах при вставке через консоль может получиться и выигрыш во времени.