MySQL load data infile json array

Question

I'd like to import a json file containing an array of jsons into a mysql table.

Using the LOAD DATA LOCAL INFILE '/var/lib/mysql-files/big_json.json' INTO TABLE test(json); give the error messageInvalid JSON text: "Invalid value." at position 1 in value for column

My json looks like the following:

[
  {
    "id": "6defd952",
    "title": "foo"
  },
  {
    "id": "98ee3d8b",
    "title": "bar"
  }
]

How can I parameterize the load command to iterate through the array?

Possible duplicate here: https://stackoverflow.com/questions/36697319/is-there-a-way-to-populate-a-mysql-5-7-table-with-data-from-a-json-file-with-lin — Matt.C, Jul 01 '17 at 13:10
Possible duplicate of [Is there a way to populate a MySQL 5.7 table with data from a json file with line breaks on Win7?](https://stackoverflow.com/questions/36697319/is-there-a-way-to-populate-a-mysql-5-7-table-with-data-from-a-json-file-with-lin) — Matt.C, Jul 01 '17 at 13:12
I have checked that question as well; no luck. Tried with comma, newlines, brackets. It is working if I separate the objects into files, but the json is far too big for that approach to be viable. — Felix Nagy, Jul 01 '17 at 13:30
An option that you can adapt and use, [https://stackoverflow.com/a/45080443](https://stackoverflow.com/a/45080443). — wchiquito, Jul 13 '17 at 12:25

score 0 · Answer 1 · answered Feb 25 '19 at 15:46

For whatever reason, MySQL errors out when you try and load pretty-printed JSON, both in 5.7 and in 8.0+, so you'll need the JSON to be in compact form, i.e.:

[{"id":"6defd952","title":"foo"},{"id":"98ee3d8b","title":"bar"}]

This can be accomplished by reading the json object to jq and using the -c flag to output in compact form to re-format the text:

Windows:

type input.json | jq -c . > infile.json

Mac/Linux:

cat input.json | jq -c . > infile.json

If you're reading from a std_out instead of a file, just replace the cat/type section and pipe to jq in the same manner. The resultant "infile.json" should then work for the LOAD DATA INFILE statement.

If you wanted to then iterate through the loaded array, use JSON_EXTRACT(JSON_FIELD, CONCAT('$[', i, ']')) with ordinal variable i to traverse the root document (array) in a single query.

MySQL load data infile json array

1 Answers1