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

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.

CodePudding user response：

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.