How to extract data from MYSQL Json extract with Key name starting with @?

s3fp2yjn 于 2022-12-22 发布在 Mysql

关注(0)|答案(1)|浏览(149)

The Below query is failing where the key name starts with [ampersand] @type. Is there a way to fetch the data without updating the Json key name in the table.

SELECT JSON_ARRAYAGG(JSON_OBJECT (
    'id',JSON_EXTRACT(INV_MASTER, "$[0].id[0]") ,
    'href',JSON_EXTRACT(INV_MASTER, "$[0].href[0]"),
    '@type',JSON_EXTRACT(INV_MASTER, "$[0].@type[0]"),
    'entityChar',JSON_EXTRACT(INV_MASTER, "$[0].entityChar")
    ))
    as MASTER from PROFILE;

This seems to be mysql not allowing some special characters in the Json functions.

mysql

来源：https://stackoverflow.com/questions/74854333/how-to-extract-data-from-mysql-json-extract-with-key-name-starting-with

1条答案

按热度按时间

pkwftd7m1#

No such problem. You use incorrect JSON paths simply.
Demo:

CREATE TABLE test (id INT, val JSON);
INSERT INTO test VALUES 
(1, '[{"id":11, "@value":"value 1"}, {"id":111, "@value":"value 11"}]'),
(2, '[{"id":22, "@value":"value 2"}, {"id":222, "@value":"value 22"}]');

SELECT id, val->>'$[0]."@value"' value1, val->>'$[1]."@value"' value2
FROM test;

id	value1	value2
1	value 1	value 11
2	value 2	value 22

fiddle
Each quote type char must be used in proper place.

Single quote ' is used for string literal quoting.
Double quote " is used for JSON path/value quoting.
Backtick ` is used for object name quoting.

In the example above - the path is string literal which is quoted with single quotes ( '$[0]."@value"' ) whereas the value of the level in the JSON path in this string value ( "@value" ) is quoted with double quotes.

赞(0）回复(0）举报 2022-12-22

我来回答

How to extract data from MYSQL Json extract with Key name starting with @?

1条答案

相关问题

热门标签

最新问答