How to handle table-valued functions like sqlite's JSON_EACH?

[justjake]

Hello, I am trying to understand how to express this in Logica:

WITH entity_item AS (
  select entity.id as entity_id, json_each(entity.items)
  from entity
)
SELECT * FROM entity_item;

Based on examples, I presume this might be expressed as:

JsonEach(val) = SqlExpr("JSON_EACH({val})", {val:});

Entity(id: 1, items: "[1, 2]");
Entity(id: 2, items: "[3, 4]");

EntityItem(entity_id:, ..r) :-
  Entity(id: entity_id, items:),
  r = JsonEach(items);

I see JSON_EACH used a lot as an implementation detail in Logica's support for sqlite as a dialect, but don't really understand the language yet. There's enough example to build some intuition but without reference documentation its hard to be successful experimenting.

I tried the above and got something close-ish to my expected SQL:

[ Error ] Error while executing SQL:
Execution failed on sql 'WITH t_1_Entity AS (SELECT * FROM (
  
    SELECT
      1 AS id,
      '[1, 2]' AS items
   UNION ALL
  
    SELECT
      2 AS id,
      '[3, 4]' AS items
  
) AS UNUSED_TABLE_NAME  )
SELECT
  Entity.id AS entity_id,
  JSON_EACH(Entity.items).*
FROM
  t_1_Entity AS Entity': near ".": syntax error

Am I doing the right thing, and this is one of those ..r not supported in sqlite issues, or is there a better way to do this?

[EvgSkv]

Per my understanding JSON_EACH in the way you use it, corresponds to in operator of Logica, which is used to run over lists.

So your example would look as follows:

@Engine("sqlite");

Entity(id: 1, items: [1, 2]);
Entity(id: 2, items: [3, 4]);

EntityItem(entity_id:, item:) :-
  Entity(id: entity_id, items:),
  item in items;

CoLab

And running it would result in:

+-----------+------+
| entity_id | item |
+-----------+------+
| 1         | 1    |
| 1         | 2    |
| 2         | 3    |
| 2         | 4    |
+-----------+------+

Hope this helps. Let me know if you have further questions!