如何从mysql中的JSON选择值 [英] How to select values from JSON in mysql

问题描述

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

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选择值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！