Как в MySQL из JSON-объекта получить массив?

Question

[Сергей Кореневский]

в таблице имеется колонка с JSON значениями, каждая из которых имеет представляет объект
{"1":1,"2":1,"72":3}
другое значение
{"11":1,"23":1}

Как видно объект имеет поля цифр.
Как этот объект получить в виде массива ?

SELECT * FROM tbl t ,
		JSON_TABLE(t.json , '$[*]'
			COLUMNS(
				_index INT PATH '$[*]'
			)
		)  as tjson

Мне нужно получить все значения после двоеточий в виде массива.

Answer 1

[Rsa97]

Массивом никак, в MySQL нет типа данных для массива.
Строкой через запятую можно

SELECT GROUP_CONCAT(`json`)
  FROM JSON_TABLE(
    '{"1":1,"2":1,"72":3}',
    '$.*' COLUMNS(`json` INT PATH '$')
  ) as `t`;

| GROUP_CONCAT(`json`) |
| -------------------- |
| 1,1,3                |

Comments

[Сергей Кореневский]

круто . А можно полученную строку как то скомбинировать с CONCAT_WS чтобы получить новый JSON и уже дисериализовать.
мне то нужно по этим цифрам найти значения из другой таблицы

[Rsa97]

Сергей Кореневский, А зачем тогда CONCAT? Без него вы получите таблицу, где каждое значение будет в отдельной строке. Делайте JOIN с этой таблицей.
А вообще, раз у вас возникла задача работать с JSON внутри MySQL, значит вы что-то делаете не так.

[Сергей Кореневский]

Rsa97, я понимаю что что-то не правильно. Но уже нет времени абсолютно всё переделывать. Есть время только JSON разворачивать, либо в PHP либо в MySQL.
Есть таблица с заказами, в полях одной колонки лежат именованные массивы.
['value_id'=>'attritute_id', ...... ];
При серриализации IDшки массива завернулись в JSON объект.
Так я хотел получить результат запроса двух таблиц.
Но вот ID второй таблицы завёрнуты в JSONы объектов первой таблицы.

[Rsa97]

Сергей Кореневский,

SELECT `table1`.*, `table2`.*
  FROM `table1`
  JOIN JSON_TABLE(`table1`.`json`, '$.*' COLUMNS(`child` INT PATH '$')) AS `tt`
  JOIN `table2` ON `table2`.`id` = `tt`.`child`

[Сергей Кореневский]

Rsa97, я наверно что то не так делаю.
в каждой ячейке колонки лежит отдельный JSON объект.
как {"1":1,"2":1,"72":3} и как {"11":1,"23":1}
т.е. получается связь таблиц один ко многим. Только в данном случае связь не во второй таблице указана, а указана в первой в виде IDшек в JSONе.

Я накидал рабочий пример с ошибкой в Онлайн редакторе.
https://www.mycompiler.io/view/9ILsDSOD3fF
Посмотрите пожалуйста!

CREATE TABLE Orders(id INTEGER, jsons VARCHAR(30));
INSERT INTO Orders(id, jsons) VALUES
(1, '{"1":1,"2":1,"72":3}'),
(2, '{"11":1,"23":1}');

SELECT o.*, a.* 
FROM Orders o
JOIN JSON_TABLE(o.jsons, '$.*' COLUMNS(`child` INT PATH '$') ) as js
JOIN Attr a ON a.attr_id = js.`child`;

[Rsa97]

Сергей Кореневский,
https://www.mycompiler.io/view/IstiFmYqkSk

-JOIN Attr a ON a.attr_id = js.`child`;
+JOIN Attr a ON a.id = js.`child`;

[Сергей Кореневский]

Rsa97, Вау. Круто, спасибо огромное.
Я специально пишу вопросы так чтобы поисковик мог правильно найти потом его ответ. Но в Tosterе Habr нет возможности делать поиск по своим же личным запросам, которые накапливаются как справочная со временем, можно только листать историю и подрят перебирать. Писал в поддержку, сказали что им пофиг.

Повторю правильный ответ тут:

CREATE TABLE Orders(id INTEGER, jsons VARCHAR(30));
INSERT INTO Orders(id, jsons) VALUES
(1, '{"1":1,"2":1,"72":3}'),
(2, '{"11":1,"23":1}');


CREATE TABLE Attr(id INTEGER, val VARCHAR(30));
INSERT INTO Attr(id, val) VALUES
(1, 'Один'),
(2, 'Два'),
(3, 'Три'),
(4, 'Четыре');


SELECT o.*, a.* 
FROM Orders o
JOIN JSON_TABLE(o.jsons, '$.*' COLUMNS(`child` INT PATH '$') ) as js
JOIN Attr a ON a.id = js.`child`;

[Сергей Кореневский]

Rsa97, Подскажите а возможно ли тоже самое сделать только с CSV содержимым,
Если например в ячейке находится "1,2,3",
Для другой таблицы я использовал функции PHP для работы с CSV. Чтобы было легко дописывать данные в ячейку.
В результате в ячейке содержится "0:F:1683481184,1:C:1683481184,3:F:1683481184,1:F:1683481184,0:F:1683481184".
Это тоже колонка в таблице заказов, первая цифра это номер подзаказа, буква статус подзаказа, а число это время в Unix.
Возможно ли так же это аналогично предыдущему разщепить. Задача: нужно находить заказы, подзаказы которых входят в временной интервал.

SELECT o.*
FROM Orders o
WHERE o.csv[*][2] >= NOW();

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

[Rsa97]

Сергей Кореневский, Вместо таких извращений приведите базу к третьей нормальной форме.
Разделить такую строку можно через рекурсивное Common Table Expression (CTE), но, IMHO, это неправильный путь.

[Сергей Кореневский]

Rsa97, Спасибо большое за наводку. Даже не знал что такие возможности есть.

CREATE TABLE Orders(id INTEGER, csv VARCHAR(80));
INSERT INTO Orders(id, csv) VALUES
(1, '0:A:1683481111,1:A:1683481112,2:A:1683481113,3:A:1683481114,4:A:1683481115'),
(2, '0:B:1683481121,1:B:1683481122,2:B:1683481123');

WITH RECURSIVE str_row  AS (
        SELECT csv
        FROM Orders
        UNION ALL
        SELECT SUBSTRING(csv,16) csv
        FROM str_row
        WHERE   LENGTH(csv) > 14   
)
SELECT SUBSTRING(csv,1,14) FROM str_row

Возвращает:

0:A:1683481111
0:B:1683481121
1:A:1683481112
1:B:1683481122
2:A:1683481113
2:B:1683481123
3:A:1683481114
4:A:1683481115

https://www.mycompiler.io/view/7EK4TDRdIsb

Когда спрашивал Вас, думал что это не реально. Разве что при помощи REPLACE() превращать строку в JSON объект.
Спасибо.

Rsa97, Ещё вопрос по главному вопросу с JSON. В Вашем примере сравнение происходит со значениями в JSON, а как сделать чтобы сравнение было с ключами (именами свойств объекта) JSON?
И в последнем примере подскажите, кроме того что это рекурсия, есть ли другие замечания?
И наверно без рекурсии такие вещи не решаются.

[Сергей Кореневский]

Rsa97, Печаль. Хостинг моего клиента не поддерживает MySQL 8.
Придётся всё делать в PHP и делать мнокократное обращение к базе.

[Руслан Федосеев]

проектируйте базу по человечески ) И не надо будет извращаться с json в полях....

[Сергей Кореневский]

Руслан Федосеев, Спасибо за совет. Будуomii_jshopping так делать всегда.
Текущий вопрос у меня приходился на модуль расширения для CMS Joomla с компонентом JoomShopping.
Как думаете у людей всегда есть возможность проектировать по человечески?
Я же не сторонник стрелять себе в ногу.