Description

JYone3A

opened on Feb 26, 2024

Issue body actions

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;

Reacted by Matheus Paes Maciel and Gabriel A. da Rosa

Activity

1. JYone3A

   changed the title [-]Call function in FROM clause (SQLite - json_each)[/-] [+]Call function in FROM clause (SQLite - json_each or MySQL - json_table)[/+] on Feb 26, 2024
2. albin3

   added

   enhancement

   on Feb 28, 2024
3. gabaoalb commented on Mar 19, 2024

   

   gabaoalb

   on Mar 19, 2024

   More actions

   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.

   Reacted by Matheus Paes Maciel
4. albin3 commented on Mar 20, 2024

   

   albin3

   on Mar 20, 2024

   Member

   More actions

   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.

   Reacted by Gabriel A. da Rosa and JYone3A
5. albin3 commented on Mar 21, 2024

   

   albin3

   on Mar 21, 2024

   Member

   More actions

   [email protected] released.

   Reacted by JYone3A

Issue actions