Table creation
create table px_projects2 (
id number generated by default on null as identity (start with 1) primary key not null
, name_jtl varchar2(500) not null constraint px_projects2_name_tl_ck CHECK (name_jtl is json(strict))
, description_jtl varchar2(500) not null constraint px_projects2_desc_tl_ck CHECK (description_jtl is json(strict))
, alias varchar2(32)
, active_ind varchar2(1) not null
, created_by varchar2(60) default
coalesce(
sys_context('APEX$SESSION','app_user')
, regexp_substr(sys_context('userenv','client_identifier'),'^[^:]*')
, sys_context('userenv','session_user')
)
not null
, created_on date default sysdate not null
, constraint px_projects2_ck_active check (active_ind in ('Y', 'N'))
)
enable primary key using index
Table created.
Comment on name_jtl
comment on column px_projects2.name_jtl is 'JSON data with the language as keys. Syntax: [{"l": "us", "tl": "Project Analysis"}]'
Statement processed.
1st insert
insert into PX_PROJECTS2 (NAME_JTL,DESCRIPTION_JTL,ALIAS,ACTIVE_IND) values (
'[{"l":"en","tl":"Name 1 - English"},
{"l":"fr","tl":"Nom 1 - Francais"},
{"l":"es","tl":"Nombre 1 - Espanol"}]'
,'[{"l":"en","tl":"Desc 1 - English"},
{"l":"fr","tl":"Desc 1 - Francais"},
{"l":"es","tl":"Desc 1 - Espanol"}]'
,'Nom 1','Y')
1 row(s) inserted.
2nd insert
insert into PX_PROJECTS2 (NAME_JTL,DESCRIPTION_JTL,ALIAS,ACTIVE_IND) values ('[{"l":"en","tl":"Name 2- English"},{"l":"fr","tl":"Nom 2 - Francais"},{"l":"es","tl":"Nom 2 - Espaniol"}]','[{"l":"en","tl":"Desc 2 - English"},{"l":"fr","tl":"Desc 2 - Francais"},{"l":"es","tl":"Desc 2 - Espaniol"}]','Nom 2','Y')
1 row(s) inserted.
"Explode" the name JSON column into rows
select t.id
, jdn.lang
, jdn.tl name
from px_projects2 t
, json_table(t.name_jtl, '$[*]'
columns (
lang path '$.l'
, tl path '$.tl'
)) jdn
where 1=1
and jdn.lang = 'fr'
| ID | LANG | NAME | 1 | fr | Nom 1 - Francais | 2 | fr | Nom 2 - Francais |
|---|
select t.id
, jdn.lang
, jdn.tl name
, jdd.lang d_lang
, jdd.tl description
from px_projects2 t
, json_table(t.name_jtl, '$[*]'
columns (
lang path '$.l'
, tl path '$.tl'
)) jdn
, json_table(t.description_jtl, '$[*]'
columns (
lang path '$.l'
, tl path '$.tl'
)) jdd
where jdn.lang = jdd.lang
and jdn.lang = 'fr'
no data found
Wide open, all rows
select t.id
, jdn.lang
, jdn.tl name
, jdd.lang d_lang
, jdd.tl description
from px_projects2 t
, json_table(t.name_jtl, '$[*]'
columns (
lang path '$.l'
, tl path '$.tl'
)) jdn
, json_table(t.description_jtl, '$[*]'
columns (
lang path '$.l'
, tl path '$.tl'
)) jdd
where jdn.lang = jdd.lang
| ID | LANG | NAME | D_LANG | DESCRIPTION | 1 | en | Name 1 - English | en | Desc 1 - English | 1 | fr | Nom 1 - Francais | fr | Desc 1 - Francais | 1 | es | Nombre 1 - Espanol | es | Desc 1 - Espanol | 2 | en | Name 2- English | en | Desc 2 - English | 2 | fr | Nom 2 - Francais | fr | Desc 2 - Francais | 2 | es | Nom 2 - Espaniol | es | Desc 2 - Espaniol |
|---|
Alternate SQL using "with" clause
with name as (
select t.id
, jdn.lang n_lang
, jdn.tl name
from px_projects2 t
, json_table(t.name_jtl, '$[*]'
columns (
lang path '$.l'
, tl path '$.tl'
)) jdn
)
, description as (
select t.id
, jdd.lang d_lang
, jdd.tl description
from px_projects2 t
, json_table(t.description_jtl, '$[*]'
columns (
lang path '$.l'
, tl path '$.tl'
)) jdd
)
select
t.id
, jdn.n_lang
, jdn.name
, jdd.description
from px_projects2 t
, name jdn
, description jdd
where t.id = jdn.id
and jdn.id = jdd.id
and jdn.n_lang = jdd.d_lang
and jdn.n_lang = 'fr'
no data found
Using no_merge
with name as (
select /*+ no_merge */
t.id
, jdn.lang n_lang
, jdn.tl name
from px_projects2 t
, json_table(t.name_jtl, '$[*]'
columns (
lang path '$.l'
, tl path '$.tl'
)) jdn
)
, description as (
select /*+ no_merge */
t.id
, jdd.lang d_lang
, jdd.tl description
from px_projects2 t
, json_table(t.description_jtl, '$[*]'
columns (
lang path '$.l'
, tl path '$.tl'
)) jdd
)
select
t.id
, jdn.n_lang
, jdn.name
, jdd.description
from px_projects2 t
, name jdn
, description jdd
where t.id = jdn.id
and jdn.id = jdd.id
and jdn.n_lang = jdd.d_lang
and jdn.n_lang = 'fr'
no data found
json_table and xmltable
select
t.id
, jdn.lang
, jdn.tl name
, jdd.tl description
, t.alias
from px_projects2 t
, json_table(t.name_jtl, '$[*]'
columns (
lang path '$.l'
, tl path '$.tl'
)) jdn
, xmltable('/json/row' passing apex_json.to_xmltype(t.description_jtl)
columns
lang varchar2(10) path 'l'
, tl varchar2(200) path 'tl'
) jdd
where jdn.lang = jdd.lang
and jdn.lang ='fr'
| ID | LANG | NAME | DESCRIPTION | ALIAS | 1 | fr | Nom 1 - Francais | Desc 1 - Francais | Nom 1 | 2 | fr | Nom 2 - Francais | Desc 2 - Francais | Nom 2 |
|---|