JSON MLS Concept

View All Scripts Login to Run Script

Script Name JSON MLS Concept
Description Storing language information on a column in JSON. Used in the APEX JTL Item plugin (https://github.com/rimblas/jtlitem)
Area SQL General / JSON
Contributor Jorge Rimblas
Created Thursday September 22, 2016

Statement 1

Notice the two columns that store data as JSON: name_jtl and description_jtl

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.

Statement 2

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.

Statement 3

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.

Statement 4

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.

Statement 5

This SQL works and returns the 'fr' values for each row.

"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

2 rows selected.

ID	LANG	NAME
1	fr	Nom 1 - Francais
2	fr	Nom 2 - Francais

Statement 6

This SQL only returns data for the 'en' language, no rows for the others.

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

Statement 7

However, you can see the data I want to get to is there!

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

6 rows selected.

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

Statement 8

Same as before using 'en' returns rows, 'fr' and 'es' do NOT.

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

Statement 9

Use the no_merge hint to stop Oracle from rewriting into one big json expression. This used to work on 12.1, but now it seems to now work correctly on 12.2 (at least inside of livesql). The other recommended approach is to create a Materialized View and create your _VL view on the MV.

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

Statement 10

This is a "hybrid" solution with json_table and xmltable and it works! why not the same with json_table?

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

2 rows selected.

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

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database