mysql json where子句

问题描述

我在表PRICING_DATA中有一个表，其中的json数据类型列如下:p

I have a table with the below json data type column in a table PRICING_DATA

pricingJson type json nullable

我正在使用sql查询表.

And I am using the sql to query the table.

select * from `PRICING_DATA` where `pricingJson`->"$.product.productFamily" = "Compute Instance";

示例json数据如下所示

The sample json data is like below

{
"product": {
    "productFamily": "Compute Instance",
    "attributes": {
        "enhancedNetworkingSupported": "Yes",.....

但是查询没有返回任何行.我在这里做什么错了?

But the query is not returning any rows.What am I doing wrong here?

数据库中的Json原始字符串似乎已转义.

Json raw string from the db seems escaped.

"{\"product\":{\"productFamily\":\"Compute Instance\",\"attributes\":{\"enhancedNetworkingSupported\":\"Yes

我使用了以下json取消引号，但仍然没有给我任何行.

I have used the below json unquote but still it is not giving me any rows.

select * from `PRICING_DATA` where JSON_UNQUOTE(JSON_EXTRACT(pricingJson, "$.product.productFamily")) = "Compute Instance";

推荐答案

您需要取消引用" JSON字符串以进行比较.

You need to "unquote" the JSON string in order to compare it.

select * from `PRICING_DATA` where `pricingJson`->>"$.product.productFamily" = "Compute Instance";

文档: https ://dev.mysql.com/doc/refman/8.0/zh-CN/json-search-functions.html#operator_json-inline-path

使用pricingJson->"$.product.productFamily"是速记

JSON_EXTRACT(pricingJson, "$.product.productFamily")

返回值，但为带引号的字符串.所以:

which returns the value, but as a quoted string. So:

SELECT pricingJson->"$.product.productFamily" FROM PRICING_DATA

将返回:

+-----------------------------------------+
| pricingJson->"$.product.productFamily"  |
+-----------------------------------------+
| "Compute Instance"                      |
+-----------------------------------------+

您需要使用JSON_UNQUOTE()函数删除引号，而使用pricingJson->>"$.product.productFamily"是以下简称:

You need to remove the quotes with the JSON_UNQUOTE() function, and using pricingJson->>"$.product.productFamily" is shorthand for:

JSON_UNQUOTE(JSON_EXTRACT(pricingJson, "$.product.productFamily"))

这篇关于mysql json where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！