3

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'.

2

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"}}]');
0

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.