- Notifications
You must be signed in to change notification settings - Fork47
Open
Labels
Description
It seems unsupported to call functions from within the FROM clause. Working example for SQLite:
SELECTone.name, group_concat(j.value,',')FROM one, json_each(one.stringArray)AS jGROUP BYone.id
Tableone:
CREATETABLEone ( idINTEGERPRIMARY KEY AUTOINCREMENT, nameTEXT, stringArrayTEXTCHECK(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 thejson_table function supported by MySQL (seehttps://dev.mysql.com/blog-archive/json_table-the-best-of-both-worlds/):
SELECT people.*FROM t1, JSON_TABLE(json_col,'$.people[*]' COLUMNS ( nameVARCHAR(40)PATH'$.name', addressVARCHAR(100)PATH'$.address') ) people;