How to create and insert a JSON object using MySQL queries?

by

On creating table set your field as JSON datatype.

CREATE TABLE `person` (
  `name` json DEFAULT NULL
);

And Insert JSON data into it,

INSERT INTO `person` (`name`)
VALUES ('["name1", "name2", "name3"]');

Or Insert JSON data by Key:Value

INSERT INTO person VALUES ('{"pid": 101, "name": "name1"}');
INSERT INTO person VALUES ('{"pid": 102, "name": "name2"}');

Select JSON data,

SELECT * FROM `person` WHERE JSON_CONTAINS(name, '["name1"]');

Note: Only supported by MySQL 5.7 (or higher) using InnoDB.

More Related Contents:

  1. What are differences between INSERT and UPDATE in MySQL?
  2. MySQL behavior of ON DUPLICATE KEY UPDATE for multiple UNIQUE fields
  3. Inserting multiple rows in mysql
  4. MySQL, update multiple tables with one query
  5. UPDATE multiple rows with different values in one query in MySQL
  6. Multiple Updates in MySQL
  7. mysql update column with value from another table
  8. Insert into multiple tables in one query
  9. MySQL error code: 1175 during UPDATE in MySQL Workbench
  10. How to convert result table to JSON array in MySQL
  11. mysql after insert trigger which updates another table’s column
  12. PHP MYSQL UPDATE if Exist or INSERT if not?
  13. How to prepend a string to a column value in MySQL?
  14. MySql update two tables at once
  15. Table is ‘read only’
  16. MySql – Way to update portion of a string?
  17. Using MySQL JSON field to join on a table
  18. MySQL Error: Incorrect usage of UPDATE and LIMIT
  19. Does MySQL overwrite a column of same value on update?
  20. Convert JSON array in MySQL to rows
  21. mySQL UPDATE query returns “0 rows affected”
  22. MySQL UPDATE and SELECT in one pass
  23. Native JSON support in MYSQL 5.7 : what are the pros and cons of JSON data type in MYSQL?
  24. How to retrieve JSON data from MySQL?
  25. How to update JSON data type column in MySQL 5.7.10?
  26. The best way to remove value from SET field?
  27. MySQL search json value by key in array
  28. SQL (MySQL) vs NoSQL (CouchDB) [closed]
  29. Update multiple rows with multiple ‘where’ clauses for each individual row
  30. MySQL update statement to store ranking positions

Leave a Comment