Как получить данные из JSON по длине строки?

161

06 сентября 2021, 09:10

Как сделать выборку из JSON? JSON представляет из себя следующее:

{"@class": "Name.Of.Class", "member": "666", "Members": [{"values": ["111", "222", "333"], "Type": "BIG", "Number": "111111", "Count": 50, "BIGUnit": 50}, {"values": ["111", "222", "333"],"BIGType":"BIG","Number": "111", "Count": 10, "BIGUnit": 10}, {"values": ["111", "222", "333"], "BIGType": "BIG", "Number": "222", "Count": 10, "BIGUnit": 10}, {"values": ["111", "222", "333"], "BIGType": "BIG","Number": "333", "Count": 10, "BIGUnit": 10}, {"values": ["111", "222", "333"], "BIGType": "BIG", "Number": "333333", "Count": 10, "BIGUnit": 10}], "ID": "07"}

Необходимо выдать все значения Number > 3.

Пробовал вот такой скрипт:

SELECT
c_date AS cd,
JSON_UNQUOTE(JSON_EXTRACT(jsonc,"$.Members[*].Number")) AS rep,
id AS did
FROM reports
WHERE length(JSON_UNQUOTE(JSON_EXTRACT(jsonc,"$.Members[*].Number"))) > 3;

c_date - Дата, jsonc - столбец с JSON, id есть id.

Причем если условие убрать, то скрипт возвращает ВСЕ значения Number, а нужны только те, что > 3. А если с условием, то возвращает NULL.

Answer 1

WITH RECURSIVE
cte1 AS ( SELECT cdate, 
                 id, 
                 JSON_EXTRACT(jsonc,"$.Members[*].Number") AS rep
          FROM reports 
        ),
cte2 AS ( SELECT 0 num
          UNION ALL
          SELECT num + 1 FROM cte2 WHERE num < ( SELECT MAX(JSON_LENGTH(rep))
                                                 FROM cte1 
                                               )
        )
SELECT cte1.cdate AS cd, 
       JSON_UNQUOTE(JSON_EXTRACT(cte1.rep, CONCAT('$[', cte2.num, ']'))) AS rep, 
       cte1.id AS did
FROM cte1, cte2
HAVING LENGTH(rep) > 3

Для версии 5+, при условии, что количество элементов Number в записи не превышает 700 и справочная база заполнена:

SELECT cte1.cdate AS cd, 
       JSON_UNQUOTE(JSON_EXTRACT(cte1.rep, CONCAT('$[', cte2.num, ']'))) AS rep, 
       cte1.id AS did
FROM ( SELECT cdate, 
              id, 
              JSON_EXTRACT(jsonc,"$.Members[*].Number") AS rep
       FROM reports ) cte1, 
     ( SELECT help_keyword_id num
       FROM mysql.help_keyword ) cte2
HAVING LENGTH(rep) > 3

Если справочная база пуста или количество значений > 700, рекомендую создать статическую таблицу с полем INT и записями от нуля до заведомо большего значения, и использовать её в подзапросе cte2.