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
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
