JSON_UNQUOTE
时间: 2023-08-04 22:07:39 浏览: 71
`JSON_UNQUOTE`是MySQL中的一个JSON函数,用于去除JSON字符串的引号。它的作用是将一个带引号的JSON字符串去除引号,并返回一个不带引号的JSON字符串。
在MySQL中,当你使用`JSON_EXTRACT`、`JSON_UNQUOTE`和其他处理JSON的函数时,有时会返回带引号的JSON字符串。如果你需要去除这些引号并得到一个纯粹的JSON字符串,可以使用`JSON_UNQUOTE`函数。
以下是一个示例:
```sql
SELECT JSON_UNQUOTE('{"name": "John"}') AS unquoted_json;
```
这个查询将返回一个不带引号的JSON字符串:
```
{ "name": "John" }
```
请注意,`JSON_UNQUOTE`函数只能去除外层的引号,无法去除嵌套在JSON字符串内部的引号。
相关问题
简化sql: select JSON_UNQUOTE(JSON_EXTRACT(woo.attachment, '$.transformerObjList')) as transformerObjList from workflow_order woo where workflow_order_id in ( select c.workflow_order_id from ( select concat( JSON_UNQUOTE(JSON_EXTRACT(wo.attachment, '$.province')), JSON_UNQUOTE(JSON_EXTRACT(wo.attachment, '$.city')), JSON_UNQUOTE(JSON_EXTRACT(wo.attachment, '$.county')), JSON_UNQUOTE(JSON_EXTRACT(wo.attachment, '$.town')), JSON_UNQUOTE(JSON_EXTRACT(wo.attachment, '$.village')) ) as address , JSON_UNQUOTE(JSON_EXTRACT(wo.attachment, '$.dataType')) as dataType ,wo.workflow_order_id from workflow_order as wo ) as c where c.dataType = 1587434126787240000 and c.address = '江苏省无锡市宜兴市A镇8村'
简化后的 SQL 查询语句如下:
```
SELECT JSON_UNQUOTE(JSON_EXTRACT(attachment, '$.transformerObjList')) AS transformerObjList
FROM workflow_order
WHERE workflow_order_id IN (
SELECT workflow_order_id
FROM (
SELECT CONCAT(
JSON_UNQUOTE(JSON_EXTRACT(attachment, '$.province')),
JSON_UNQUOTE(JSON_EXTRACT(attachment, '$.city')),
JSON_UNQUOTE(JSON_EXTRACT(attachment, '$.county')),
JSON_UNQUOTE(JSON_EXTRACT(attachment, '$.town')),
JSON_UNQUOTE(JSON_EXTRACT(attachment, '$.village'))
) AS address,
JSON_UNQUOTE(JSON_EXTRACT(attachment, '$.dataType')) AS dataType,
workflow_order_id
FROM workflow_order
) AS c
WHERE c.dataType = 1587434126787240000 AND c.address = '江苏省无锡市宜兴市A镇8村'
)
```
简化sql: select cc.villageMaxDocumentCnt from ( select concat ( JSON_UNQUOTE(JSON_EXTRACT(wo.attachment,'$.province')), JSON_UNQUOTE(JSON_EXTRACT(wo.attachment,'$.city')), JSON_UNQUOTE(JSON_EXTRACT(wo.attachment,'$.county')), JSON_UNQUOTE(JSON_EXTRACT(wo.attachment,'$.town')), JSON_UNQUOTE(JSON_EXTRACT(wo.attachment,'$.village')) ) as address , JSON_UNQUOTE(JSON_EXTRACT(wo.attachment, '$.villageMaxDocumentCnt')) as villageMaxDocumentCnt from workflow_order as wo where name = '地区审批单' ) cc where cc.address = '黑龙江省哈尔滨市阿城区舍利镇丰收村' and cc.villageMaxDocumentCnt is not null ;
简化后的 SQL 查询语句如下:
```
SELECT cc.villageMaxDocumentCnt
FROM (
SELECT CONCAT(
JSON_UNQUOTE(JSON_EXTRACT(attachment, '$.province')),
JSON_UNQUOTE(JSON_EXTRACT(attachment, '$.city')),
JSON_UNQUOTE(JSON_EXTRACT(attachment, '$.county')),
JSON_UNQUOTE(JSON_EXTRACT(attachment, '$.town')),
JSON_UNQUOTE(JSON_EXTRACT(attachment, '$.village'))
) AS address,
JSON_UNQUOTE(JSON_EXTRACT(attachment, '$.villageMaxDocumentCnt')) AS villageMaxDocumentCnt
FROM workflow_order
WHERE name = '地区审批单'
) cc
WHERE cc.address = '黑龙江省哈尔滨市阿城区舍利镇丰收村' AND cc.villageMaxDocumentCnt IS NOT NULL;
```