Call function in FROM clause (SQLite - json_each or MySQL - json_table)

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;

I'm facing a similar problem with table functions in oracle:

SELECT DISTINCT CODIGO_PEDIDO,
       EMPRESA,
       FILIAL,
       TIPODOC,
       ORDCOMPRA,
       TRANSPORTADORA,
       FRETE,
       DTINCLUSAO,
       DTEMISSAO,
       TOTALOC,
       CREATEDBY,
       COMUNICA,
       MOEDA,
       OBSERVACOES,
       FORNECEDOR,
       CONDICAO,
       GRUPO,
       TOTALFINANC         
  FROM 
 TABLE(EFPGE029.EFFPE029013(1,1,0,2,1))

this code throws me a error in js-sql-parser but is totally ok for my Oracle database.

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)) and JSON_TABLE are not supported yet.

js-sql-parser@1.6.0 released.

Navigation Menu

Repository navigation

Call function in FROM clause (SQLite - json_each or MySQL - json_table) #60

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Call function in FROM clause (SQLite - json_each or MySQL - json_table) #60

Description

Activity

gabaoalb commented on Mar 19, 2024

albin3 commented on Mar 20, 2024

albin3 commented on Mar 21, 2024

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions