I'm trying to pull data from json and insert it into a table. New rows appear in the table by number as in json, but all fields have an empty value, NULL. Please help me figure it out.
[{"name":"ivan","city":"london","kurs":"1"},{"name":"lena","city":"tokio","kurs":"5"},{"name":"misha","city":"kazan","kurs":"3"}]
SET @json = CONVERT(LOAD_FILE('/var/lib/mysql-files/myfile.json') using utf8mb4);
REPLACE INTO test (name, city, kurs)
SELECT
JSON_VALUE(@json, '$.name') as name,
JSON_VALUE(@json, '$.city') as city,
JSON_VALUE(@json, '$.kurs') as kurs
FROM JSON_TABLE(@json,'$[*]' COLUMNS (data JSON PATH '$')) jsontable
CodePudding user response:
Use this, (and change VARCAR(20)
to the appropriate definition):
set @json = '[{"name":"ivan","city":"london","kurs":"1"},{"name":"lena","city":"tokio","kurs":"5"},{"name":"misha","city":"kazan","kurs":"3"}]';
select * from json_table(@json,'$[*]' columns(name varchar(20) path '$.name',
city varchar(20) path '$.city',
kurs varchar(20) path '$.kurs')) as jsontable;
see: DBFIDDLE
output:
name | city | kurs |
---|---|---|
ivan | london | 1 |
lena | tokio | 5 |
misha | kazan | 3 |