maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

Hello Igor,


with recursive cte1 as (
  select * 
  from
     t1,
     json_table(t1.item_props,'$' columns( color varchar(100) path '$.color')) as t
), 
cte2 as (
  select * 
  from 
    (select 1 as n from dual) x, 
    cte1 
  union all 
  select 
    cte2.n+1, 
    cte2.item_name, 
    cte2.item_props, 
    cte2.color 
  from
    cte2,
    json_table(cte2.item_props,'$' columns( color varchar(100) path '$.color')) as t 
  where
    t.item_name=cte2.item_name and 
    cte2.n < 3
) 
select * from cte2;
ERROR 1054 (42S22): Unknown column 't.item_name' in 'where clause'


with recursive cte1 as (
  select *
  from
    t1,
    json_table(t1.item_props,'$' columns( color varchar(100) path '$.color')) as t
),
cte2 as (
  select *
  from
    (select 1 as n from dual) x,
    cte1
  union all
  select
    cte2.n+1,
    cte2.item_name,
    cte2.item_props,
    cte2.color
  from
    cte2,
    cte1 as t
  where
    t.item_name=cte2.item_name and cte2.n < 3
)
select * from cte2;
+------+-----------+-----------------------------------+-------+
| n    | item_name | item_props                        | color |
+------+-----------+-----------------------------------+-------+
|    1 | Laptop    | {"color": "black", "price": 1000} | black |
|    1 | Jeans     | {"color": "blue", "price": 50}    | blue  |
|    2 | Laptop    | {"color": "black", "price": 1000} | black |
|    2 | Jeans     | {"color": "blue", "price": 50}    | blue  |
|    3 | Laptop    | {"color": "black", "price": 1000} | black |
|    3 | Jeans     | {"color": "blue", "price": 50}    | blue  |
+------+-----------+-----------------------------------+-------+

Igor> It means that json table t does not inherit the column name item_name from cte2.

I'm not sure what do you mean by "inherit"?  json_table t has one column named
"color".

Could you elaborate?

BR
 Sergei
-- 
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net