Есть вариант сохранить в поле json (mysql) массив с нужным порядком?

Question

[lolrofl01]

Добрый день.
Есть массив вида:

$arr = [
  'a' => 1,
  'b' => 2,
  'c' => 3
];

Я его превращаю в json (json_encode), и кладу в бд. Так фишка в том, что в бд порядок меняется, условно скажем, после того, как вытащишь из бд и превратишь обратно в массив, он уже принимает вот такой вид:

$arr = [
  'c' => 3
  'a' => 1,
  'b' => 2
];

Есть вариант сохранить порядок? Читал пару статей, пишут, что это нормальное поведение, мол так в бд оптимизируется json. Как-то это отключить можно? Или единственный вариант, это сохранять вместе с этим массивом массив $order и в нем прописывать ключи от А до Я в нужном порядке, затем в цикле опираться на этот массив?

Comments

[Александр Карабанов]

Но зачем? Доступ к значению всё равно по ключу, какая разница какой у ключей порядок.

[lolrofl01]

Александр Карабанов, Для вывода на вьюшку без доп танцев. Т.е. вытащил массив - кинул на фронтенд и там циклом распарсил. Порядок то нужный. И ладно, если бы ключи были числовыми, можно было бы сортировку перед циклом вставить. Но ключи текстовые...

Answer 1

[Akina]

фишка в том, что в бд порядок меняется, условно скажем, после того, как вытащишь из бд и превратишь обратно в массив, он уже принимает вот такой вид

Согласно описанию формата, порядок элементов внутри объекта не существует (вернее, не определён), и его поддержание не гарантируется. В отличие от массива.
Так что если порядок важен, то каждая запись должна быть преобразована в отдельный объект, а все они - сложены в массив в нужном порядке, т.е. JSON должен выглядеть, например, так:
'[{"a":1}, {"b":2}, {"c":3}]'

Я его превращаю в json (json_encode), и кладу в бд.

Покажите точно, как выглядит этот JSON.
Покажите точный CREATE TABLE таблицы, в которую кладёте.
Покажите, как точно выглядит в таблице положенное туда JSON значение.
Покажите точно это же JSON значение после обратного извлечения.

Да, всё это - для одного и того же JSON.

Answer 2

[Антон Шаманов]

mysql порядок передаваемых значений по умолчанию не изменяет.
если тебе не нужен доступ через sql к значениям в json, то храни json строку в blob.

Comments

[lolrofl01]

Да в том и дело, что доступ нужен. Я в прошлый раз хранил в varchar строку, тогда доступ не был нужен. Кстати, вопрос, в blog лучше хранить json или в longtext, если доступ к json не нужен, но в нем будет хранится очень много данных, включая текст размером 20-40к символов?

[Akina]

mysql порядок передаваемых значений по умолчанию не изменяет.

Если тип поля JSON, а тип JSON-документа (или субдокумента) объект - изменяет.

[Антон Шаманов]

Akina, добавил 100 рандомных записей в таблицу id:int:auto_increment:primary_key, data_string: text - исходная строка с json в формате {"%s": %d, "%s": %d, "%s": %d}, data_json:json содержит json строку как и data_string. как итог: никаких изменений

[Akina]

Антон Шаманов,

data_string: text

Вы, извините, каким местом читаете?
TEXT - вот ни разу не JSON.

[Антон Шаманов]

Akina, это скорее каким местом ты читаешь?

data_json:json содержит json строку как и data_string.

data_json используется для хранения исходного варианта и поэтому тип поля text

[Akina]

Антон Шаманов,

data_json используется для хранения исходного варианта и поэтому тип поля text

Потому значение и остаётся как было, что оно TEXT. Даже ты, прекрасно на глаз определяющий, что некое значение есть валидный JSON, никогда не сможешь определить, является ли показанное тебе значение значением типа JSON, или это просто текст, который число случайно может быть интерпретирован как валидный JSON. А сервер этого делать просто не будет. Ему сказали - текст, и всё, вот хоть война, хоть застрелись, а это текст, и его надо хранить так, как было.

DEMO FIDDLE. TEXT и JSON в одном флаконе, разница видна невооружённым глазом.

[Антон Шаманов]

Akina, все, теперь вижу. у меня был косяк с генерацией json - свойства объекта всегда шли в правильном порядке: `{"a": ..., "b": ..., "c": ...}`. MySql сортирует объекты по свойствам наподобие PHP ksort(). Правда xз при каких ситуациях это играет роль - порядок свойств в объекте ведь не важен, при сравнении `json_encode('{"a": 1,"b":2}') == json_encode('{"b":2,"a": 1}')`

[Akina]

Антон Шаманов,

xз при каких ситуациях это играет роль - порядок свойств в объекте ведь не важен

ЕМНИП стандарт не накладывает требования уникальности на имя свойства в объекте...

[Антон Шаманов]

Akina, очень может быть, а вот php очень даже накладывает)

[Антон Шаманов]

Akina, если ты о https://www.json.org/json-en.html то это abnf для парсера/токенайзера

[Akina]

Антон Шаманов, я про RFC 7158, 7159, 8927 и пр.
Так-то да, отсутствие такого требования с учётом отсутствия понятия относительного местоположения - нелогично. Потому практически все реализации такое требование уникальности реализуют, либо в крайнем случае объявляют несуществующим взаимные порядок для совпадающих.
Собственно, обычная трудность... то же наблюдается, скажем, с агрегированием и с оконными функциями в SQL

[Антон Шаманов]

Akina, я не понимаю почему это тебя беспокоит - '{"a": 1, "a": 2}' сохраняется в JSON поле как '{"a": 2}'

[Akina]

Антон Шаманов,

я не понимаю почему это тебя беспокоит - '{"a": 1, "a": 2}' сохраняется в JSON поле как '{"a": 2}'

В MySQL - да.
В MariaDB - нет, что положил, то и получишь.
Oracle 21c не сохранит, ругнётся на дублирование.
PostgerSQL поведёт себя как MariaDB. Но если сменить тип данных на JSONB - то как MySQL.
SQLite ведёт себя как MariaDB - и для JSON, и для JSONB.
Остальные популярные СУБД скажут "А чой-та за тип данных такой?"

Это при прямой вставке одного документа, имеющего дублирование имени свойства. Уже как бы безрадостно. Хотя и есть чёткое деление - в зависимости от того, используется ли бинарное представление или текстовое.

Ещё хуже становится, когда выполняется добавление одного документа ко второму (слияние). С одной стороны, RFC 7396. С другой, операция, выполняемая JSON_MERGE_PRESERVE в MySQL (вот лень проверять остальные СУБД) не кажется мне нелогичной...

[Антон Шаманов]

Akina, в вопросе указано что речь о mysql т.ч. нет смысла рассматривать другие субд

[Антон Шаманов]

Akina, учитывая что данные передаются из php и конвертируются с помощью json_encode(), то подобные проблемы не актуальны

[Akina]

Антон Шаманов,

в вопросе указано что речь о mysql т.ч. нет смысла рассматривать другие субд

Заданный мне вопрос о том, почему меня это беспокоит, и на который я ответил, не связан с СУБД.

учитывая что данные передаются из php и конвертируются с помощью json_encode(), то подобные проблемы не актуальны

Для вопроса автора как раз актуальны - собственно, именно у автора всё и корячит от изменения физического порядка.

данные передаются из php и конвертируются с помощью json_encode()

Я до сих пор так и не увидел, как точно всё это выглядит. Вы пытаетесь описывать то, как это выглядит со стороны PHP, тогда как я прошу показать, как оно же выглядит со стороны MySQL. Я знаю, что PHP-шники практически всегда не заморачиваются на этот вопрос, хотя напрасно - обычно именно так и можно увидеть суть их проблем.

И, по-моему, автор вообще уже восклал на этот свой вопрос и занялся чем-то другим.

[lolrofl01]

Akina,

И, по-моему, автор вообще уже восклал на этот свой вопрос и занялся чем-то другим.

Ничего подобного. Наблюдаю. Вашу информацию про массив принял к сведению, но пока не проверил. Вопрос задал немного заранее. Проверю или сегодня ночью или завтра днем. Если все так - отмечу решением. Если нет - напишу Вам, может поможете разобраться))

В любом случае, за ответ большое спасибо!

[Akina]

lolrofl01,

Изучите внимательно DEMO FIDDLE - там всё показано, когда порядок меняется, когда нет.

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

[lolrofl01]

Akina, Изучил - там все замечательно, как мне и надо. Но по опыту знаю, то, что работает у всех, запросто может не работать у меня и наоборот)

[lolrofl01]

Akina, Чудеса случаются - это я вам как программист говорю))

[Антон Шаманов]

lolrofl01, хреновый из тебя значит программист (без обид)

[Akina]

Но по опыту знаю, то, что работает у всех, запросто может не работать у меня и наоборот)

Обычно это означает, что пропущена какая-то с виду совсем мелочь, но которая влияет на результат. Хороший программист найдёт источник проблемы, поймёт причину проблемы, и больше такой ошибки не допустит.

[lolrofl01]

Антон Шаманов, А ты из тех единорогов, у которых всегда все с первого раза работает как надо, компилятор никогда не ругается и любой баг ты предвидишь за 100 строчек до его допущения? Какие-то выводы у тебя малоумные, мягко говоря.

[lolrofl01]

Akina, Ну так и живем, да, поэтому и надо все проверить самому. Сейчас дописываю систему как раз, уже почти дошел до нужной стадии.

[Akina]

lolrofl01, поведение, продемонстрированное в fiddle, в MySQL наблюдается стабильно. Оно определяется внутренним бинарным форматом представления документов JSON. Можете доверять этому факту. Если он и обвалится - то только если программисты в очередной версии начнут переделывать этот блок кода (что само по себе вряд ли) и залепят там какую-то багу, которая к тому же не выявится на этапе тестирования.

Реально же в MySQL проблемы с JSON - иногда есть. В последней (8,0,28) версии они что-то намудрили с multivalued indices, что в определённых условиях приводит к потере записей в выходном наборе. Но это экзотика, которую, скорее всего, поправят.

[lolrofl01]

Akina, все проверил, порядок не меняется, как вы и сказали. Спасибо большое!)

[Антон Шаманов]

lolrofl01, нет, я не из единорогов, я из программистов - читаю мануалы, пишу тесты, а если начинают творится "чудеса", то пишу отчет об ошибке или шлю PR разработчику этого "чуда".