MySQL嵌套JSON列搜索并提取子JSON [英] MySQL nested JSON column search and extract sub JSON
问题描述
我有一个MySQL表authors，其中有列id，name和published_books.在此，published_books是JSON列.有了示例数据，
I have a MySQL table authors with columns id, name and published_books. In this, published_books is a JSON column. With sample data,
id | name | published_books
-----------------------------------------------------------------------
1 | Tina | {
| | "17e9bf8f": {
| | "name": "Book 1",
| | "tags": [
| | "self Help",
| | "Social"
| | ],
| | "language": "English",
| | "release_date": "2017-05-01"
| | },
| | "8e8b2470": {
| | "name": "Book 2",
| | "tags": [
| | "Inspirational"
| | ],
| | "language": "English",
| | "release_date": "2017-05-01"
| | }
| | }
-----------------------------------------------------------------------
2 | John | {
| | "8e8b2470": {
| | "name": "Book 4",
| | "tags": [
| | "Social"
| | ],
| | "language": "Tamil",
| | "release_date": "2017-05-01"
| | }
| | }
-----------------------------------------------------------------------
3 | Keith | {
| | "17e9bf8f": {
| | "name": "Book 5",
| | "tags": [
| | "Comedy"
| | ],
| | "language": "French",
| | "release_date": "2017-05-01"
| | },
| | "8e8b2470": {
| | "name": "Book 6",
| | "tags": [
| | "Social",
| | "Life"
| | ],
| | "language": "English",
| | "release_date": "2017-05-01"
| | }
| | }
-----------------------------------------------------------------------
如您所见，published_books列具有嵌套的JSON数据(一级). JSON将具有动态UUID作为键，并且其值将作为书的详细信息作为JSON.
As you see, the published_books column has nested JSON data (one level). JSON will have dynamic UUIDs as the keys and its values will be book details as a JSON.
我想在特定条件下搜索books，然后仅提取这些图书的JSON数据以作为结果返回.
I want to search for books with certain conditions and extract those books JSON data alone to return as the result.
我编写的查询，
select JSON_EXTRACT(published_books, '$.*') from authors
where JSON_CONTAINS(published_books->'$.*.language', '"English"')
and JSON_CONTAINS(published_books->'$.*.tags', '["Social"]');
此查询执行搜索并返回整个published_books JSON.但是我只希望这些书仅使用JSON.
This query performs the search and returns the entire published_books JSON. But I wanted just those books JSON alone.
预期结果，
result
--------
"17e9bf8f": {
"name": "Book 1",
"tags": [
"self Help",
"Social"
],
"language": "English",
"release_date": "2017-05-01"
}
-----------
"8e8b2470": {
"name": "Book 6",
"tags": [
"Social",
"Life"
],
"language": "English",
"release_date": "2017-05-01"
}
推荐答案
尚无JSON函数可使用类似"WHERE"的逻辑过滤文档或数组的元素.
There is no JSON function yet that filters elements of a document or array with "WHERE"-like logic.
但这是某些使用JSON数据的人可能想要完成的任务，因此MySQL提供的解决方案是使用
But this is a task that some people using JSON data may want to do, so the solution MySQL has provided is to use the JSON_TABLE() function to transform the JSON document into a format as if you had stored your data in a normal table. Then you can use a standard SQL WHERE clause to the fields returned.
您不能在MySQL 5.7中使用此功能，但是如果升级到MySQL 8.0，则可以执行此操作.
You can't use this function in MySQL 5.7, but if you upgrade to MySQL 8.0 you can do this.
select authors.id, authors.name, books.* from authors,
json_table(published_books, '$.*'
columns(
bookid for ordinality,
name text path '$.name',
tags json path '$.tags',
language text path '$.language',
release_date date path '$.release_date')
) as books
where books.language = 'English'
and json_search(tags, 'one', 'Social') is not null;
+----+-------+--------+--------+-------------------------+----------+--------------+
| id | name | bookid | name | tags | language | release_date |
+----+-------+--------+--------+-------------------------+----------+--------------+
| 1 | Tina | 1 | Book 1 | ["self Help", "Social"] | English | 2017-05-01 |
| 3 | Keith | 2 | Book 6 | ["Social", "Life"] | English | 2017-05-01 |
+----+-------+--------+--------+-------------------------+----------+--------------+
请注意，即使使用JSON_TABLE()，嵌套的JSON数组仍然很难使用.在此示例中，我将tags作为JSON数组公开，然后使用JSON_SEARCH()查找所需的标签.
Note that nested JSON arrays are still difficult to work with, even with JSON_TABLE(). In this example, I exposed the tags as a JSON array, and then use JSON_SEARCH() to find the tag you wanted.
我同意Rick James的观点-您最好将数据存储在规范化的表和列中.您认为使用JSON可以节省您一些工作，但事实并非如此.也许可以更方便地将数据存储为单个JSON文档，而不是将多个表中的多行数据存储起来，但是您只需要再次解开JSON，然后就可以按照自己的方式查询它了.
I agree with Rick James — you might as well store the data in normalized tables and columns. You think that using JSON will save you some work, but it's won't. It might make it more convenient to store the data as a single JSON document instead of multiple rows across several tables, but you just have to unravel the JSON again before you can query it the way you want.
此外，如果您将数据存储在JSON中，则每次要查询数据时，都必须解决这种JSON_TABLE()表达式.这将为您带来更多的工作而不是正常存储数据.
Furthermore, if you store data in JSON, you will have to solve this sort of JSON_TABLE() expression every time you want to query the data. That's going to make a lot more work for you on an ongoing basis than if you had stored the data normally.
坦率地说，我还没有在Stack Overflow上看到一个关于在MySQL上使用JSON的问题，这不会得出这样的结论:如果数据结构不正确，在关系表中存储数据比使用JSON更好.不需要改变.
Frankly, I have yet to see a question on Stack Overflow about using JSON with MySQL that wouldn't lead to the conclusion that storing data in relational tables is a better idea than using JSON, if the structure of the data doesn't need to vary.
这篇关于MySQL嵌套JSON列搜索并提取子JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
