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

Introduction and usage summary of React-Native screenshot component react-native-view-shot

Table of contents 1. Phenomenon 2. Solution 3. Su...

Introduction to using Unicode characters in web pages (&#,\u, etc.)

The earliest computers could only use ASCII chara...

Nginx reverse proxy learning example tutorial

Table of contents 1. Reverse proxy preparation 1....

Solution to the error when calling yum in docker container

When executing yum in dockerfile or in the contai...

MySQL 8.0.21.0 Community Edition Installation Tutorial (Detailed Illustrations)

1. Download MySQL Log in to the MySQL official we...

Practical method of deleting a row in a MySql table

First, you need to determine which fields or fiel...

Detailed explanation of the conflict between flex layout and position:absolute/fixed

I encountered this problem before when developing...

MySQL Index Optimization Explained

In daily work, we sometimes run slow queries to r...

How to quickly build your own server detailed tutorial (Java environment)

1. Purchase of Server 1. I chose Alibaba Cloud...

How to import CSS styles into HTML external style sheets

The link-in style is to put all the styles in one...

Detailed explanation of the problem of matching even when there is a space at the end of the string in the Mysql query condition

1. Table structure TABLE person id name 1 you 2 Y...

Robots.txt detailed introduction

Robots.txt is a plain text file in which website ...

Detailed explanation of installing jdk1.8 and configuring environment variables in a Linux-like environment

The configuration is very simple, but I have to c...

JavaScript Prototype Details

Table of contents 1. Overview 1.1 What is a proto...

Detailed explanation of MySQL delayed replication library method

Simply put, delayed replication is to set a fixed...