'How to get data from json in a mysql query correctly?

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

enter image description here

mysqljson


Solution 1:[1]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Luuk

Related Questions