How to select values from JSON in MySQL?

Issue

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'));

The error that I get is:

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

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

Solution

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;

Answered By – Alex Alves

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0