MySQL按数组中的键搜索JSON值 [英] MySQL search json value by key in array

问题描述

我有一个JSON对象数组，想返回一个特定的节点.为了简化我的问题，可以说数组看起来像这样:

[
    {"Race": "Orc", "strength": 14},
    {"Race": "Knight", "strength": 7},
    ...
]

例如，我想知道骑士的实力. 函数 JSON_SEARCH ，返回路径'$[1].Race'，并带有解决方案

从本质上讲，您的意思是申请

这些是在SQL中使用WHERE子句和列的SELECT-列表完成的，但是使用JSON进行相同的操作就很难通过JSON_SEARCH()和JSON_CONTAINS()之类的功能轻松实现.

MySQL 8.0提供的解决方案是 JSON_TABLE( )函数将JSON文档转换为虚拟派生表-就像您已定义常规行和列一样.如果JSON是您所描述的格式(一个对象数组)，那么它将起作用.

这是我通过将示例数据插入表中进行的演示:

create table mytable ( mycol json );

insert into mytable set mycol = '[{"Race": "Orc", "strength": 14}, {"Race": "Knight", "strength": 7}]';

SELECT j.* FROM mytable, JSON_TABLE(mycol, 
  '$[*]' COLUMNS (
    race VARCHAR(10) PATH '$.Race', 
    strength INT PATH '$.strength'
  )
) AS j;
+--------+----------+
| race   | strength |
+--------+----------+
| Orc    |       14 |
| Knight |        7 |
+--------+----------+

现在，您可以执行通常使用SELECT查询执行的操作，例如选择和投影:

SELECT j.strength FROM mytable, JSON_TABLE(mycol, '$[*]' 
  COLUMNS (
    race VARCHAR(10) PATH '$.Race', 
    strength INT PATH '$.strength'
  )
) AS j 
WHERE j.race = 'Orc'
+----------+
| strength |
+----------+
|       14 |
+----------+

这有两个问题:

1. 您需要每次 进行此操作，或者查询JSON数据，或者创建一个VIEW来执行此操作.

2. 您说过，您不知道属性字段，但是要编写JSON_TABLE()查询，您必须指定要搜索的属性并在查询中进行投影.您不能将其用于完全未定义的数据.

我已经回答了很多有关在MySQL中使用JSON的类似问题.我观察到，当您想做这种事情时，将JSON文档当作表对待，以便可以将WHERE子句中的条件应用于JSON数据中的字段，那么所有查询都会变得更加困难.然后，您开始觉得花费几分钟时间来定义属性会更好，因此您可以编写更简单的查询.

I have an array of JSON objects and want to have a specific node returned. To simplify my problem, lets say the array could look like this:

[
    {"Race": "Orc", "strength": 14},
    {"Race": "Knight", "strength": 7},
    ...
]

And I want to know the strength of the knight for example. The function JSON_SEARCH, returns the path '$[1].Race' and with the path operator I could get the strength. Is there a way to combine those two, so I could do something like the following?

SELECT someFunc(myCol,'$[*].Race','Orc','$.strength') AS strength
FROM myTable

I am using MySQL 8.0.15.

解决方案

You're essentially meaning to apply selection and projection to the array elements and object fields of your JSON document. You need to do something like a WHERE clause to select a "row" within the array, and then do something like picking one of the fields (not the one you used in your selection criteria).

These are done in SQL using the WHERE clause and the SELECT-list of columns, but doing the same with JSON isn't something you can do easily with functions like JSON_SEARCH() and JSON_CONTAINS().

The solution MySQL 8.0 provides is the JSON_TABLE() function to turn a JSON document into a virtual derived table — as though you had defined conventional rows and columns. It works if the JSON is in the format you describe, an array of objects.

Here's a demo I did by inserting your example data into a table:

create table mytable ( mycol json );

insert into mytable set mycol = '[{"Race": "Orc", "strength": 14}, {"Race": "Knight", "strength": 7}]';

SELECT j.* FROM mytable, JSON_TABLE(mycol, 
  '$[*]' COLUMNS (
    race VARCHAR(10) PATH '$.Race', 
    strength INT PATH '$.strength'
  )
) AS j;
+--------+----------+
| race   | strength |
+--------+----------+
| Orc    |       14 |
| Knight |        7 |
+--------+----------+

Now you can do things you normally do with SELECT queries, like selection and projection:

SELECT j.strength FROM mytable, JSON_TABLE(mycol, '$[*]' 
  COLUMNS (
    race VARCHAR(10) PATH '$.Race', 
    strength INT PATH '$.strength'
  )
) AS j 
WHERE j.race = 'Orc'
+----------+
| strength |
+----------+
|       14 |
+----------+

This has a couple of problems:

1. You need to do this every time you query the JSON data, or else create a VIEW to do it.

2. You said you don't know the attribute fields, but to write a JSON_TABLE() query, you must specify the attributes you want to search and project in your query. You can't use this for totally undefined data.

I've answered quite a number of similar questions about using JSON in MySQL. I've observed that when you want to do this sort of thing, treating a JSON document like a table so you can apply condition in the WHERE clause to fields within your JSON data, then all your queries get a lot more difficult. Then you start feeling like you would have been better off spending a few minutes to define your attributes so you could write simpler queries.

这篇关于MySQL按数组中的键搜索JSON值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！