Forum Stats

  • 3,815,381 Users
  • 2,259,010 Discussions
  • 7,893,063 Comments

Discussions

Date conversion from JSON into mview

I am running 21c on-prem with a JSON table with JSON data type. In the JSON document there is a dateofbirth field in a 14 digits number format (YYYYMMDDHH24MISS). I like to create a fast refresh materialized view and convert the number to a date without time on the fly. Is there a way to handle the conversion in the create mview statement? Below is my original script without date conversion. Thanks.


CREATE MATERIALIZED VIEW mv_test

BUILD IMMEDIATE REFRESH FAST ON COMMIT WITH PRIMARY KEY

AS SELECT t.id, jt.*

    FROM test t,

      json_table(t.json_document, '$' ERROR ON ERROR NULL ON EMPTY

       COLUMNS (

       order_id    NUMBER     PATH '$.orderid',

       date_of_birth    NUMBER      PATH '$.dateofbirth'

           )) jt;

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,327 Red Diamond

    And to answer fast MV part - you need MV log + PK:

    drop materialized view test_mv
    /
    drop table test purge
    /
    create table test(id,json_document)
      as
        select 1,'{"orderid":123, "dateofbirth":20220512123456 }' from dual
    /
    alter table test
      add constraint test_pk
        primary key(id)
    /
    create materialized view log on test
    /
    create materialized view test_mv
    build immediate
    refresh fast
    on commit
    as
    select t.id, jt.order_id, to_date(substr(jt.date_of_birth,1,8),'YYYYMMDD') dob
    from test t,json_table(
        t.json_document, '$' error on error null on empty
        columns (
           order_id         NUMBER          path '$.orderid',
           date_of_birth    VARCHAR2(14)    path '$.dateofbirth'
        )
    ) jt
    /
    
    Materialized view created.
    
    SQL>
    

    Or, if table has no PK, MV log with rowid:

    drop materialized view test_mv
    /
    drop table test purge
    /
    create table test(id,json_document)
      as
        select 1,'{"orderid":123, "dateofbirth":20220512123456 }' from dual
    /
    create materialized view log on test with rowid
    /
    create materialized view test_mv
    build immediate
    refresh fast
    on commit
    with rowid
    as
    select t.rowid rid,t.id, jt.order_id, to_date(substr(jt.date_of_birth,1,8),'YYYYMMDD') dob
    from test t,json_table(
        t.json_document, '$' error on error null on empty
        columns (
           order_id         NUMBER          path '$.orderid',
           date_of_birth    VARCHAR2(14)    path '$.dateofbirth'
        )
    ) jt
    /
    
    Materialized view created.
    
    SQL>
    

    Replace ON COMMIT with ON DEMAND, create refresh group, add MV to id and create refresh group refresh job if you want to refresh MV on some schedule.

    SY.

Answers

  • cormaco
    cormaco Member Posts: 1,900 Silver Crown

    Here is an example:

    with test(id,json_document) as (
        select 1,'{"orderid":123, "dateofbirth":20220512123456 }' from dual
    )
    select t.id, jt.order_id, to_date(substr(jt.date_of_birth,1,8),'YYYYMMDD') dob
    from test t,json_table(
        t.json_document, '$' error on error null on empty
        columns (
           order_id         NUMBER          path '$.orderid',
           date_of_birth    VARCHAR2(14)    path '$.dateofbirth'
        )
    ) jt;
    
            ID   ORDER_ID DOB     
    ---------- ---------- --------
             1        123 12.05.22
    
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,327 Red Diamond

    And to answer fast MV part - you need MV log + PK:

    drop materialized view test_mv
    /
    drop table test purge
    /
    create table test(id,json_document)
      as
        select 1,'{"orderid":123, "dateofbirth":20220512123456 }' from dual
    /
    alter table test
      add constraint test_pk
        primary key(id)
    /
    create materialized view log on test
    /
    create materialized view test_mv
    build immediate
    refresh fast
    on commit
    as
    select t.id, jt.order_id, to_date(substr(jt.date_of_birth,1,8),'YYYYMMDD') dob
    from test t,json_table(
        t.json_document, '$' error on error null on empty
        columns (
           order_id         NUMBER          path '$.orderid',
           date_of_birth    VARCHAR2(14)    path '$.dateofbirth'
        )
    ) jt
    /
    
    Materialized view created.
    
    SQL>
    

    Or, if table has no PK, MV log with rowid:

    drop materialized view test_mv
    /
    drop table test purge
    /
    create table test(id,json_document)
      as
        select 1,'{"orderid":123, "dateofbirth":20220512123456 }' from dual
    /
    create materialized view log on test with rowid
    /
    create materialized view test_mv
    build immediate
    refresh fast
    on commit
    with rowid
    as
    select t.rowid rid,t.id, jt.order_id, to_date(substr(jt.date_of_birth,1,8),'YYYYMMDD') dob
    from test t,json_table(
        t.json_document, '$' error on error null on empty
        columns (
           order_id         NUMBER          path '$.orderid',
           date_of_birth    VARCHAR2(14)    path '$.dateofbirth'
        )
    ) jt
    /
    
    Materialized view created.
    
    SQL>
    

    Replace ON COMMIT with ON DEMAND, create refresh group, add MV to id and create refresh group refresh job if you want to refresh MV on some schedule.

    SY.