Summary of related functions for Mysql query JSON results

Summary of related functions for Mysql query JSON results

The JSON format field is a new attribute added in MySQL 5.7, but it is essentially saved in the database as a string. When I first came into contact with it, I only knew the $.xx method of querying fields, because most of the time, this is enough, and the rest can be left to the program. However, some recent operations require more complex query operations, so I quickly learned more methods.

JSON_EXTRACT(json_doc [,path])

Query Fields

mysql> set @j = '{"name":"wxnacy"}';
mysql> select JSON_EXTRACT(@j, '$.name');
+----------------------------+
| JSON_EXTRACT(@j, '$.name') |
+----------------------------+
| "wxnacy" |
+----------------------------+

There is also a more concise way, but it can only be used when querying the table

mysql> select ext -> '$.name' from test;
+-----------------+
| ext -> '$.name' |
+-----------------+
| "wxnacy" |
+-----------------+

After $., you can use the JSON format to obtain data, such as arrays.

mysql> set @j = '{"a": [1, 2]}';
mysql> select JSON_EXTRACT(@j, '$.a[0]');
+----------------------------+
| JSON_EXTRACT(@j, '$.a[0]') |
+----------------------------+
| 1 |
+----------------------------+

JSON_DEPTH(json_doc)

Calculate the depth of JSON. The calculation method is {} []. If there is a symbol, it is a layer. If there is data under the symbol, an additional layer is added. Complex JSON is calculated until the deepest one. The official document says that the null value has a depth of 0, but the actual effect is not the case. Here are a few examples

JSON_LENGTH(json_doc [, path])

Calculates the length of the outermost JSON or the specified path. The scalar length is 1. The length of an array is the number of array elements, and the length of an object is the number of object members.

mysql> SELECT JSON_LENGTH('[1, 2, {"a": 3}]');
+---------------------------------+
| JSON_LENGTH('[1, 2, {"a": 3}]') |
+---------------------------------+
| 3 |
+---------------------------------+
mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}');
+-----------------------------------------+
| JSON_LENGTH('{"a": 1, "b": {"c": 30}}') |
+-----------------------------------------+
| 2 |
+-----------------------------------------+
mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b');
+------------------------------------------------+
| JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b') |
+------------------------------------------------+
| 1 |
+------------------------------------------------+

JSON_TYPE(json_doc)

Returns a utf8mb4 string indicating the type of the JSON value. This can be an object, array, or scalar type, as shown below:

mysql> SET @j = '{"a": [10, true]}';
mysql> SELECT JSON_TYPE(@j);
+---------------+
| JSON_TYPE(@j) |
+---------------+
| OBJECT |
+---------------+
mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a'));
+------------------------------------+
| JSON_TYPE(JSON_EXTRACT(@j, '$.a')) |
+------------------------------------+
| ARRAY |
+------------------------------------+
mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a[0]'));
+---------------------------------------+
| JSON_TYPE(JSON_EXTRACT(@j, '$.a[0]')) |
+---------------------------------------+
| INTEGER |
+---------------------------------------+
mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a[1]'));
+---------------------------------------+
| JSON_TYPE(JSON_EXTRACT(@j, '$.a[1]')) |
+---------------------------------------+
| BOOLEAN |
+---------------------------------------+

Possible return types

Pure JSON type:

  • OBJECT: JSON object
  • ARRAY: JSON array
  • BOOLEAN: JSON true and false text
  • NULL: JSON null literal

Number Type:

  • INTEGER: MySQL TINYINT, SMALLINT, MEDIUMINT, and INT and BIGINT scalars
  • DOUBLE: MySQL DOUBLE FLOAT scalar
  • DECIMAL: MySQL DECIMAL and NUMERIC scalars

Time Type:

  • DATETIME: MySQL DATETIME and TIMESTAMP scalars
  • Date: MySQL DATE scalar
  • TIME: MySQL TIME scalar

String type:

STRING: MySQL utf8 character type scalars: CHAR, VARCHAR, TEXT, ENUM, and SET

Binary Type:

BLOB: MySQL binary type scalars, including BINARY, VARBINARY, BLOB, and BIT

All other types:

OPAQUE (raw bit)

JSON_VALID

Returns 0 or 1 to indicate whether the value is valid JSON. If the argument is NULL, returns NULL.

mysql> SELECT JSON_VALID('{"a": 1}');
+------------------------+
| JSON_VALID('{"a": 1}') |
+------------------------+
| 1 |
+------------------------+
mysql> SELECT JSON_VALID('hello'), JSON_VALID('"hello"');
+---------------------+-----------------------+
| JSON_VALID('hello') | JSON_VALID('"hello"') |
+---------------------+-----------------------+
| 0 | 1 |
+---------------------+-----------------------+

The above is the detailed content of the summary of related functions for Mysql query JSON results. For more information about MySQL json functions, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • MySql fuzzy query json keyword retrieval solution example
  • Example code for converting Mysql query result set into JSON data
  • Detailed explanation of querying JSON format fields in MySQL
  • MySQL json format data query operation
  • Which is faster among MySQL full-text index, joint index, like query, and json query?
  • Two query methods when the MySQL query field type is json
  • Python query mysql, return json instance
  • How to query json in the database in mysql5.6 and below
  • Mysql directly queries the data in the stored Json string

<<:  Detailed explanation of the visualization component using Vue to compare the differences between two sets of data

>>:  Solve the problem of multiple listeners reported when starting tomcat in Idea

Recommend

React implements a general skeleton screen component example

Table of contents What is a skeleton screen? Demo...

CentOS8 - bash: garbled characters and solutions

This situation usually occurs because the Chinese...

Detailed explanation of mysql backup and recovery

Preface: The previous articles introduced the usa...

A few experiences in self-cultivation of artists

As the company's influence grows and its prod...

About the pitfall record of Vue3 transition animation

Table of contents background Problem location Fur...

Graphic tutorial on configuring nginx file server in windows 10 system

Download the Windows version of Nginx from the Ng...

Pitfalls based on MySQL default sorting rules

The default varchar type in MySQL is case insensi...

Detailed explanation of the usage of MySQL memory tables and temporary tables

Usage of MySQL memory tables and temporary tables...

Cross-browser development experience summary (I) HTML tags

Add a DOCTYPE to the page Since different browser...

Detailed explanation of how Zabbix monitors the master-slave status of MySQL

After setting up the MySQL master-slave, you ofte...

td content automatically wraps table table td after setting the width too much text automatically wraps

Set the table's style="table-layout:fixed...

A quick solution to accidentally delete MySQL data (MySQL Flashback Tool)

Overview Binlog2sql is an open source MySQL Binlo...

How to center the entire page content so that the height can adapt to the content and automatically expand

How to center the entire page content and how to m...

MySQL 8.0.12 Quick Installation Tutorial

The installation of MySQL 8.0.12 took two days an...

Node.js adds, deletes, modifies and checks the actual combat record of MySQL database

Table of contents Three steps to operate the data...