-
Notifications
You must be signed in to change notification settings - Fork 46
Open
Labels
@JYone3A
Description
It seems unsupported to call functions from within the FROM clause. Working example for SQLite:
SELECT one.name, group_concat(j.value, ', ') FROM one, json_each(one.stringArray) AS j GROUP BY one.id
Table one:
CREATE TABLE one ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, stringArray TEXT CHECK(json_valid(stringArray)) )
Data:
INSERT INTO "one" ("id", "name", "stringArray") VALUES ('1', 'John Doe', '["apple","banana","cherry"]'); INSERT INTO "one" ("id", "name", "stringArray") VALUES ('2', 'Alice Smith', '["banana","grape","cherry"]'); INSERT INTO "one" ("id", "name", "stringArray") VALUES ('3', 'Bob Johnson', '["banana","apple","grape"]');
Update 1: also does not work with the json_table function supported by MySQL (see https://dev.mysql.com/blog-archive/json_table-the-best-of-both-worlds/):
SELECT people.* FROM t1, JSON_TABLE(json_col, '$.people[*]' COLUMNS ( name VARCHAR(40) PATH '$.name', address VARCHAR(100) PATH '$.address') ) people;