如何从mysql中的JSON选择值

问题描述：

您好，有人可以告诉我此查询出了什么问题.

Hi can anyone tell me what is wrong with this query.

DECLARE @json LONGTEXT;

SET @json = '[ { "name":"John Smith",  "address":"780 Mission St, San Francisco, CA 94103"}, { "name":"Sally Brown",  "address":"75 37th Ave S, St Cloud, MN 94103"}, { "name":"John Johnson",  "address":"1262 Roosevelt Trail, Raymond, ME 04071"}     ]';

##SELECT @json;

SELECT * FROM JSON_TABLE (@json, '$[*]' COLUMNS (
                name VARCHAR(40)  PATH '$.name',
                address VARCHAR(100) PATH '$.address'));

我得到的错误是

您的SQL语法有错误；检查手册对应于您的MySQL服务器版本以使用正确的语法靠近'(@json，'$ [*]'栏( 名称VARCHAR(40)PATH'$ .name'， '在第1行

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(@json, '$[*]' COLUMNS ( name VARCHAR(40) PATH '$.name', ' at line 1

仅供参考，我使用TOAD作为工具将sql实例连接到云.

Just for Information I am using TOAD as tool to connect to my cloud my sql instance.

答

首先，在这种情况下，在mysql中无需声明变量.只需使用"SET"关键字即可.最后，您需要为选择"查询放置一个别名.像这样:

First, in mysql you don't need to declare a variable, in this case. Just use 'SET' keyword. And finaly, you need put an alias for your 'select' query. Like this:

SET @json = '[ { "name":"John Smith",  "address":"780 Mission St, San Francisco, CA 94103"}, { "name":"Sally Brown",  "address":"75 37th Ave S, St Cloud, MN 94103"}, { "name":"John Johnson",  "address":"1262 Roosevelt Trail, Raymond, ME 04071"}     ]';

##SELECT @json;

SELECT * FROM JSON_TABLE (@json, '$[*]' COLUMNS (
                `name` VARCHAR(40)  PATH '$.name',
                `address` VARCHAR(100) PATH '$.address')) AS T;

如何从mysql中的JSON选择值

相关推荐