Repository navigation
Call function in FROM clause (SQLite - json_each or MySQL - json_table) #60
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.idTable 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;
Activity
[-]Call function in FROM clause (SQLite - json_each)[/-][+]Call function in FROM clause (SQLite - json_each or MySQL - json_table)[/+]gabaoalb commentedon Mar 19, 2024
I'm facing a similar problem with table functions in oracle:
this code throws me a error in js-sql-parser but is totally ok for my Oracle database.
albin3 commentedon Mar 20, 2024
select * FROM function(xxx, xxx)like sql will be supported since v1.6.0.Nested function call like
TABLE(EFPGE029.EFFPE029013(1,1,0,2,1))andJSON_TABLEare not supported yet.albin3 commentedon Mar 21, 2024
js-sql-parser@1.6.0released.