0

We have one business scenario: each id has sevaral codes, each code has "current, max, min" value. But each id's code are not same, so we use json to store them in PostgreSQL 12 {"code1":[cur,min,max], "code2":[cur,min,max],..."

We want to extract these json for analysis, so we use json_each() to extract key-value in rows

The problems is: We should realize same function in MySQL 5.7, no json_table() or json_each()

here are example in PostgreSQL:

test=# create table test (id int, data json);
CREATE TABLE


test=# insert into test values (1, '{"1":[0,0,1], "2":[0,1,0]}'), (2, '{"2":[0,0,1], "3":[0,1,0], "4":[0,0,0]}');
INSERT 0 2


test=# select * from test;
 id |                    data
----+-----------------------------------------
 1  | {"1":[0,0,1], "2":[0,1,0]}
 2  | {"2":[0,0,1], "3":[0,1,0], "4":[0,0,0]}


test=# select id, key::int as code, value->>0 as cur_val, value->>1 as min_val, value->>2 as max_val from test, json_each(data);
 id | code | cur_val | min_val | max_val
----+------+---------+---------+---------
 1  |    1 | 0       | 0       | 1
 1  |    2 | 0       | 1       | 0
 2  |    2 | 0       | 0       | 1
 2  |    3 | 0       | 1       | 0
 2  |    4 | 0       | 0       | 0

how to realize it in MySQL 5.7

yezhy
  • 1
  • Shown values contains 2 and 3 codes per one value. Does some limit for codes per object amount exists? – Akina Aug 22 '23 at 13:45
  • Being 5.7 (MySQL), perhaps the solution is not so simple. Are we talking about millions of records? One possible option would be a stored procedure, cursor and temporary table can get the expected result. – wchiquito Aug 22 '23 at 17:34
  • less then 1000 codes per one value, usually 20 codes @Akina – yezhy Aug 23 '23 at 00:51
  • the table will have 40 millions records, each record will have 20-30 codes. @wchiquito – yezhy Aug 23 '23 at 00:54
  • Due to the number of records, my option will not be the one that scales best (I would rule it out). – wchiquito Aug 23 '23 at 09:45
  • do you have some good suggestions?@wchiquito – yezhy Aug 24 '23 at 01:12
  • Use synthetic table with numbers, combine them to paths and extract. – Akina Aug 24 '23 at 08:28

0 Answers0