JSON array format in MySQL not working?

Question

What is the proper way to do a JSON data array in MySQL?

Following the JSON.org example http://json.org/example.html does not work:

 insert into zz_TEST_ObsJSON (jsonData) values ('{"Test1":{"Val1":"37", "Val2":"25"}},{"Test2":{"Val1":"25", "Val2":"27"}}');

ERROR 3140 (22032): Invalid JSON text: "The document root must not follow by other values." at position 36 in value for column 'zz_TEST_ObsJSON.jsonData'.

For giggles, tried putting it all in another { }:

insert into zz_TEST_ObsJSON (jsonData) values ('{{"Test1":{"Val1":"37", "Val2":"25"}},{"Test2":{"Val1":"25", "Val2":"27"}}}');

ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 1 in value for column 'zz_TEST_ObsJSON.jsonData'.

This other method that kind of looks like geoJSON does not either:

insert into zz_TEST_ObsJSON (jsonData) values ('[["Test1":{"Val1":"37", "Val2":"25"}],["Test2":{"Val1":"25", "Val2":"27"}]]');

ERROR 3140 (22032): Invalid JSON text: "Missing a comma or ']' after an array element." at position 10 in value for column 'zz_TEST_ObsJSON.jsonData'.

insert into zz_TEST_ObsJSON (jsonData) values ('["Test1":{"Val1":"37", "Val2":"25"}],["Test2":{"Val1":"25", "Val2":"27"}]');

ERROR 3140 (22032): Invalid JSON text: "Missing a comma or ']' after an array element." at position 9 in value for column 'zz_TEST_ObsJSON.jsonData'.

Answer 1

Are you attempting to insert a JSON array with two objects, Test1 and Test2?

If so, then you need to insert the data as an array:

JSON array:

[{
  "Test1": {
    "Val1": "37",
    "Val2": "25"
  }
}, {
  "Test2": {
    "Val1": "25",
    "Val2": "27"
  }
}]

Your INSERT statement would look similar to the following (note the wrapping brackets around the JSON objects):

INSERT INTO zz_TEST_ObsJSON (jsonData) values ('[{"Test1":{"Val1":"37", "Val2":"25"}},{"Test2":{"Val1":"25", "Val2":"27"}}]');

Answer 2

Your ownly problem here is that you don't understand JSON. In the database JSON can store any one JavaScript Object,

object
array
string
number
"true"
"false"
"null"

• In the first example, you try to store two objects. You can't do that. You can only store an object (which may itself have objects as keys).
• In the second example, you try to store an object with key that is itself an object. You can't do that either.
• In the third example, you try to construct an array with elements of [k:v].
• In the fourth example, you try to store two arrays. You can't do that. You can only store an array (which may itself have arrays as elements).