Content-Length: 260587 | pFad | http://github.com/JavaScriptor/js-sql-parser/issues/60

5C Call function in FROM clause (SQLite - json_each or MySQL - json_table) · Issue #60 · JavaScriptor/js-sql-parser · GitHub
Skip to content

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

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Open
JYone3A opened this issue Feb 26, 2024 · 3 comments
Open

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

JYone3A opened this issue Feb 26, 2024 · 3 comments
Labels
enhancement

Comments

@JYone3A
Copy link

JYone3A commented Feb 26, 2024 •
edited
Loading

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;
@JYone3A JYone3A changed the title Call function in FROM clause (SQLite - json_each) Call function in FROM clause (SQLite - json_each or MySQL - json_table) Feb 26, 2024
@gabaoalb
Copy link

gabaoalb commented Mar 19, 2024 •
edited
Loading

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.

@albin3 albin3 mentioned this issue Mar 20, 2024
Merged
@albin3
Copy link
Member

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

@albin3
Copy link
Member

albin3 commented Mar 21, 2024

js-sql-parser@1.6.0 released.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
enhancement
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@albin3 @gabaoalb @JYone3A








ApplySandwichStrip

pFad - (p)hone/(F)rame/(a)nonymizer/(d)eclutterfier!      Saves Data!


--- a PPN by Garber Painting Akron. With Image Size Reduction included!

Fetched URL: http://github.com/JavaScriptor/js-sql-parser/issues/60

Alternative Proxies:

Alternative Proxy

pFad Proxy

pFad v3 Proxy

pFad v4 Proxy