Geek Answers Handbook

Is there a way to use json objects in SQL

Here's the problem:

We have a MySQL database that stores JSON strings. Until now, this has not been a problem, but suddenly our client asked us to perform a search / order / other operations with these objects. Since the projects are in a rather late state, the transition to Mongo is not the choice that the team leader wants to make, so I ask if there is a quick way to use these JSON strings?

+4
source share
3 answers

JSON as intention

JSON MySQL. JSON, , , , "" JSON, ( )., , ( ) MySQL JSON: , JSON - . , - - , . , JSON, , , , , .

:

  • Postgree SQL, json, 9.4 jsonb . , RDBMS, , .
  • ( ), . , - , , . , , ( ).
  • , JSON ( DB) . - , , .. , , .
  • JSON MySQL. ? , JSON, JSON , , , . UDF ( CREATE FUNCTION). , . : . , Mongo, . . , - mysql-unit , MySQL 5.6 (, , .. )

, MySQL 5.7, JSON - , - JSON-, MySQL 5.7. () , , . , , , , :

CREATE FUNCTION json_append       RETURNS string  SONAME 'libmy_json_udf.so';
CREATE FUNCTION json_valid        RETURNS integer SONAME 'libmy_json_udf.so';
CREATE FUNCTION json_extract      RETURNS string  SONAME 'libmy_json_udf.so';
CREATE FUNCTION json_replace      RETURNS string  SONAME 'libmy_json_udf.so';
CREATE FUNCTION json_remove       RETURNS string  SONAME 'libmy_json_udf.so';
CREATE FUNCTION json_set          RETURNS string  SONAME 'libmy_json_udf.so';
CREATE FUNCTION json_merge        RETURNS string  SONAME 'libmy_json_udf.so';
CREATE FUNCTION json_search       RETURNS string  SONAME 'libmy_json_udf.so';
CREATE FUNCTION json_contains_key RETURNS integer SONAME 'libmy_json_udf.so';

.

+3

- MySQL, PostgreSQL JSONB.

wiki- PostgreSQL 9.4, , JSON . , JSON ( , PostgreSQL, , JSONB:

table booksdata ( 
    title text not null,
    isbn text not null primary key,
    pubinfo jsonb not null 
)

, , :

SELECT avg((pubinfo #>> '{"cost"}')::NUMERIC) 
FROM booksdata
WHERE pubinfo @> '{ "publisher" : "It Books" }';
0

MySQL5.7 JSON - https://dev.mysql.com/doc/refman/5.7/en/json.html

, NoSQL.

mysql> SELECT JSON_ARRAY('a', 1, NOW());
0

More articles:


All Articles