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

Best Practices Guide for MySQL Partitioned Tables

Preface: Partitioning is a table design pattern. ...

TCP third handshake data transmission process diagram

The process packets with the SYN flag in the RFC7...

JS realizes special effects of web page navigation bar

This article shares with you a practical web navi...

MySQL 8.0.15 installation and configuration graphic tutorial

This article records the installation and configu...

An article to deal with Mysql date and time functions

Table of contents Preface 1. Get the current time...

JavaScript implements cool mouse tailing effects

After watching this, I guarantee that you have ha...

Linux file system operation implementation

This reading note mainly records the operations r...

How to use CSS styles and selectors

Three ways to use CSS in HTML: 1. Inline style: s...

Detailed explanation of four solutions for implementing in-line scrolling on mobile devices

Discovering Needs If only part of an area is allo...

How to implement nested if method in nginx

Nginx does not support nested if statements, nor ...

43 Web Design Mistakes Web Designers Should Watch Out For

This is an article about website usability. The a...

Detailed explanation of nodejs built-in modules

Table of contents Overview 1. Path module 2. Unti...

Introduction to using window.open, a jump menu that opens in a new window

Copy code The code is as follows: <pre> <...

Writing a shell script in Ubuntu to start automatically at boot (recommended)

The purpose of writing scripts is to avoid having...

Specific steps for Vue browser to return monitoring

Preface When sharing a page, you hope to click th...