Forum Stats

  • 3,874,846 Users
  • 2,266,781 Discussions
  • 7,911,983 Comments

Discussions

Trying to convert multiple rows for a given id to a json table

Brian___B
Brian___B Member Posts: 9 Green Ribbon
edited Nov 3, 2022 1:49PM in General Database Discussions

We are still using 12c, but upgrading. We have a tall skinny table that for each distinct job_id value has multiple component_id/component_value combinations. The table is more complicated than this example and has over 1 billion rows. I did not design this and there is enough code already written against this table that it would be hard to refactor the way that the data is stored.

Currently we have code that joins to this table multiple times for each component_id that we are filtering by. I thought that this would be easier if I aggregate by job_id and convert the component_id/component_value columns to json. I could then use json_query for each column searching by component_id for each component_id that we are trying to look up.

I have tried different functions, but I can't find a function that would create a json object and allow me to pull out a single value based on the two id columns.

Can someone help me to find the right function to create a json object in a query and pull out the data based on the job_id and component_id values? There is sample code to build a table.

Thanks,

CREATE TABLE job_component_values
 ( job_id     NUMBER,
  component_id  NUMBER,
  component_value VARCHAR2( 128 ),
  CONSTRAINT job_component_values_pkey
  PRIMARY KEY ( job_id, component_id ) );



Tagged:
«1

Answers

  • Saubhik
    Saubhik Member Posts: 5,926 Gold Crown

    What is your expected output?

    select
        json_object ('job_id' value job_id,
                      'component_ids' value
                                json_arrayagg (component_id),
                      'component_value' value
                                json_arrayagg (component_value)
                    )
    from job_component_values
    group by job_id;
    
    

    {

    "job_id" : 0,

    "component_ids" : [2,13,12,11,10,9,8,7,6,5,4,3],

    "component_value" : ["CODE2","CODE13","CODE12","CODE11","CODE10","CODE9","CODE8","CODE7","CODE6","CODE5","CODE4","CODE3"]

    }

    The output will be like above.

    Brian___B
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,949 Red Diamond
    edited Nov 3, 2022 1:10PM

    @Saubhik: careful. We are not guaranteed two arrayagg's aggregate in same order. We should use:

    select  json_object(
                        'job_id' value job_id,
                        'component_ids' value json_arrayagg(component_id order by component_id),
                        'component_value' value json_arrayagg(component_value order by component_id)
                       )
      from  job_component_values
      group by job_id;
    

    SY.

    SaubhikBrian___B
  • Saubhik
    Saubhik Member Posts: 5,926 Gold Crown

    Yes SY, you are correct, ORDER BY should be included.

    Brian___B
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,949 Red Diamond

    In general, I'd do something like:

    select  json_object(
                        'job_id' value job_id,
                        'components' value json_arrayagg(
                                                         json_object(
                                                                     'id' is component_id,
                                                                     'value' is component_value
                                                                    )
                                                        )
                       )
      from  job_component_values
      group by job_id;
    
    
    

    SY.

  • Brian___B
    Brian___B Member Posts: 9 Green Ribbon

    Thanks Saubhik and Solomon. I meant to add some examples on how the data is queried. But the site threw errors when I tried to save the question. The first query shows the way that the table is used today. The table is joined to the larger query once for each component_id that is desired. Each number translates to a finite component and needs to be joined to the larger for each component that is needed. So if we want 12+ components, that becomes 12+ joins. Unfortunately we don't have the table partitioning option. If we did we could partition by component_id and make it more efficient. It really is a bad design.

    When I saw this, I thought that if we could aggregate the data by job_id and create some kind of JSON object where I could pull out the correct component_values based on the component_ids for a given job_id. When I do this the JSON_QUERY doesn't return any data. It seems like to should and I can't seem to figure out what I would need to do to get the data back from the JSON object.

    If I can figure this out, there might be an opportunity to create a new table storing the component_ids and component_values as JSON, making the table a little wider, but much smaller. We should also be able to index the JSON to speed the return of the data. It should be much easier to manage as well.

    Thanks for the help!

    SELECT jcv.job_id, job_component_values_3.component_value_3, job_component_values_5.component_value_5, 
                       job_component_values_7.component_value_7, job_component_values_9.component_value_9
      FROM system.job_component_values -- The real query would join to a different table and get thge job_id, 
                                       --  then join to this table  multiple times to get the various component values based on component id
      LEFT OUTER JOIN ( SELECT job_id, component_id AS component_id_3, component_value AS component_value_3 
                         FROM system.job_component_values WHERE component_id = 3 )                           job_component_values_3
        ON jcv.job_id = job_component_values_9.job_id
      LEFT OUTER JOIN ( SELECT job_id, component_id AS component_id_5, component_value AS component_value_5 
                         FROM system.job_component_values WHERE component_id = 5 )                           job_component_values_5 
        ON jcv.job_id = job_component_values_9.job_id
      LEFT OUTER JOIN ( SELECT job_id, component_id AS component_id_7, component_value AS component_value_7 
                         FROM system.job_component_values WHERE component_id = 7 )                           job_component_values_7 
        ON jcv.job_id = job_component_values_9.job_id
      LEFT OUTER JOIN ( SELECT job_id, component_id AS component_id_9, component_value AS component_value_9 
                         FROM system.job_component_values WHERE component_id = 9 )                           job_component_values_9 
        ON jcv.job_id = job_component_values_9.job_id )
     ORDER BY 1;
    
    
    
    
    SELECT jcv.job_id,
           JSON_QUERY(component_ids,'$.component_id[3].component_value' PRETTY) AS job_component_values_3,
           JSON_QUERY(component_ids,'$.component_id[5].component_value' PRETTY) AS job_component_values_5,
           JSON_QUERY(component_ids,'$.component_id[7].component_value' PRETTY) AS job_component_values_7,
           JSON_QUERY(component_ids,'$.component_id[9].component_value' PRETTY) AS job_component_values_9
      FROM ( SELECT job_id,
                    json_objectagg( component_id VALUE component_value )  AS component_ids,
                    COUNT(*)                                              AS cnt
               FROM ( SELECT job_id, component_value,
                             CAST( component_id AS VARCHAR2(1024) )       AS component_id
                        FROM system.job_component_values
                       WHERE job_id = 87632874 )
              GROUP BY job_id );
    
    
    
    
    
    
    
  • Brian___B
    Brian___B Member Posts: 9 Green Ribbon

    I tried this query two different ways and still I am not getting the data out.

    SQL> SELECT job_id,
      2         JSON_VALUE( '{component_id:3}', '$.component_value' ) AS job_component_values_3,
      3         JSON_VALUE( '{component_id:5}', '$.component_value' ) AS job_component_values_5,
      4         JSON_VALUE( '{component_id:7}', '$.component_value' ) AS job_component_values_7,
      5         JSON_VALUE( '{component_id:9}', '$.component_value' ) AS job_component_values_9
      6    FROM ( SELECT job_id, json_object( 'job_id' value job_id,
      7                                       'components' value json_arrayagg( json_object( 'id' is component_id, 'value' is component_value ) ) ) AS component_ids
      8             FROM system.job_component_values
      9            WHERE job_id = 7
     10            GROUP BY job_id )
     11   ORDER BY job_id;
    
    
        JOB_ID JOB_COMPONENT_V JOB_COMPONENT_V JOB_COMPONENT_V JOB_COMPONENT_V
    ---------- --------------- --------------- --------------- ---------------
             7
    
    
    1 row selected.
    
    
    Elapsed: 00:00:00.00
    SQL> SELECT job_id,
      2         JSON_VALUE( '{component_id:3}', '$.component_value' ) AS job_component_values_3,
      3         JSON_VALUE( '{component_id:5}', '$.component_value' ) AS job_component_values_5,
      4         JSON_VALUE( '{component_id:7}', '$.component_value' ) AS job_component_values_7,
      5         JSON_VALUE( '{component_id:9}', '$.component_value' ) AS job_component_values_9
      6    FROM ( SELECT job_id, json_object( 'components' value json_arrayagg( json_object( 'id' is component_id, 'value' is component_value ) ) ) AS component_ids
      7             FROM system.job_component_values
      8            WHERE job_id = 7
      9            GROUP BY job_id )
     10   ORDER BY job_id;
    
    
        JOB_ID JOB_COMPONENT_V JOB_COMPONENT_V JOB_COMPONENT_V JOB_COMPONENT_V
    ---------- --------------- --------------- --------------- ---------------
             7
    
    
    1 row selected.
    
    
    
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,949 Red Diamond

    I don't have your data, so I give you example using SCOTT EMP table:

    with t as (
               select  deptno,
                       json_object(
                                   'dept' value deptno,
                                   'employees' value json_arrayagg(
                                                                    json_object(
                                                                                'ename' is ename,
                                                                                'salary' is sal
                                                                               )
                                                                   )
                                  ) j
                 from  emp
                 group by deptno
              )
    select  cast(json_value(j,'$.employees[*]?(@.ename == "KING").salary') as number) king_salary,
            cast(json_value(j,'$.employees[*]?(@.ename == "CLARK").salary') as number) clark_salary
      from  t
      where deptno = 10
    /
    
    KING_SALARY CLARK_SALARY
    ----------- ------------
           5000         2450
    
    SQL>
    

    Or, using JSON_TABLE:

    with t as (
               select  deptno,
                       json_object(
                                   'dept' value deptno,
                                   'employees' value json_arrayagg(
                                                                    json_object(
                                                                                'ename' is ename,
                                                                                'salary' is sal
                                                                               )
                                                                   )
                                  ) j
                 from  emp
                 group by deptno
              )
    select  jt.king_salary,
            jt.clark_salary
      from  t,
            json_table(
                       j,
                       '$.employees'
                       columns(
                               king_salary number path '$[*]?(@.ename == "KING").salary',
                               clark_salary number path '$[*]?(@.ename == "CLARK").salary'
                              )
                      ) jt
      where deptno = 10
    /
    
    KING_SALARY CLARK_SALARY
    ----------- ------------
           5000         2450
    
    SQL>
    

    SY.

  • Brian___B
    Brian___B Member Posts: 9 Green Ribbon

    Hi Solomon,

    The original post has a SQL file that creates the table and inserts data. The cardinality is more like a hash map where you look for a particular component_id and get a single component_value in return. So If I look for a component_id of 3, I should be able to get it to return a single component_value. I'm not sure how your code sample fits into that.

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

    Not everyone is willing to open files from unknown (no offence) sources. Post small set of insert statements. But you should be able to use my example to correct your SQL.

    SY.

  • Brian___B
    Brian___B Member Posts: 9 Green Ribbon

    Here is the SQL


    SET ECHO ON;
    
    
    CREATE TABLE system.job_component_values
      ( job_id          NUMBER,
        component_id    NUMBER,
        component_value VARCHAR2( 128 ),
        CONSTRAINT system.job_component_values_pkey
        PRIMARY KEY ( job_id, component_id ) );
    
    INSERT INTO system.job_component_values VALUES ( 0,2,'CODE2');
    INSERT INTO system.job_component_values VALUES ( 0,3,'CODE3');
    INSERT INTO system.job_component_values VALUES ( 0,4,'CODE4');
    INSERT INTO system.job_component_values VALUES ( 0,5,'CODE5');
    INSERT INTO system.job_component_values VALUES ( 0,6,'CODE6');
    INSERT INTO system.job_component_values VALUES ( 0,7,'CODE7');
    INSERT INTO system.job_component_values VALUES ( 0,8,'CODE8');
    INSERT INTO system.job_component_values VALUES ( 0,9,'CODE9');
    INSERT INTO system.job_component_values VALUES ( 0,10,'CODE10');
    INSERT INTO system.job_component_values VALUES ( 0,11,'CODE11');
    INSERT INTO system.job_component_values VALUES ( 0,12,'CODE12');
    INSERT INTO system.job_component_values VALUES ( 0,13,'CODE13');
    INSERT INTO system.job_component_values VALUES ( 1,1,'CODE1');
    INSERT INTO system.job_component_values VALUES ( 1,2,'CODE2');
    INSERT INTO system.job_component_values VALUES ( 1,3,'CODE3');
    INSERT INTO system.job_component_values VALUES ( 1,4,'CODE4');
    INSERT INTO system.job_component_values VALUES ( 1,5,'CODE5');
    INSERT INTO system.job_component_values VALUES ( 1,6,'CODE6');
    INSERT INTO system.job_component_values VALUES ( 1,7,'CODE7');
    INSERT INTO system.job_component_values VALUES ( 1,8,'CODE8');
    INSERT INTO system.job_component_values VALUES ( 1,9,'CODE9');
    INSERT INTO system.job_component_values VALUES ( 1,10,'CODE10');
    INSERT INTO system.job_component_values VALUES ( 1,11,'CODE11');
    INSERT INTO system.job_component_values VALUES ( 1,12,'CODE12');
    INSERT INTO system.job_component_values VALUES ( 1,13,'CODE13');
    INSERT INTO system.job_component_values VALUES ( 2,1,'CODE1');
    INSERT INTO system.job_component_values VALUES ( 2,2,'CODE2');
    INSERT INTO system.job_component_values VALUES ( 2,3,'CODE3');
    INSERT INTO system.job_component_values VALUES ( 2,4,'CODE4');
    INSERT INTO system.job_component_values VALUES ( 2,5,'CODE5');
    INSERT INTO system.job_component_values VALUES ( 2,6,'CODE6');
    INSERT INTO system.job_component_values VALUES ( 2,7,'CODE7');
    INSERT INTO system.job_component_values VALUES ( 2,8,'CODE8');
    INSERT INTO system.job_component_values VALUES ( 2,9,'CODE9');
    INSERT INTO system.job_component_values VALUES ( 2,10,'CODE10');
    INSERT INTO system.job_component_values VALUES ( 2,11,'CODE11');
    INSERT INTO system.job_component_values VALUES ( 2,12,'CODE12');
    INSERT INTO system.job_component_values VALUES ( 2,13,'CODE13');
    INSERT INTO system.job_component_values VALUES ( 3,1,'CODE1');
    INSERT INTO system.job_component_values VALUES ( 3,2,'CODE2');
    INSERT INTO system.job_component_values VALUES ( 3,3,'CODE3');
    INSERT INTO system.job_component_values VALUES ( 3,4,'CODE4');
    INSERT INTO system.job_component_values VALUES ( 3,5,'CODE5');
    INSERT INTO system.job_component_values VALUES ( 3,6,'CODE6');
    INSERT INTO system.job_component_values VALUES ( 3,7,'CODE7');
    INSERT INTO system.job_component_values VALUES ( 3,8,'CODE8');
    INSERT INTO system.job_component_values VALUES ( 3,9,'CODE9');
    INSERT INTO system.job_component_values VALUES ( 3,10,'CODE10');
    INSERT INTO system.job_component_values VALUES ( 3,11,'CODE11');
    INSERT INTO system.job_component_values VALUES ( 3,12,'CODE12');
    INSERT INTO system.job_component_values VALUES ( 3,13,'CODE13');
    INSERT INTO system.job_component_values VALUES ( 4,1,'CODE1');
    INSERT INTO system.job_component_values VALUES ( 4,2,'CODE2');
    INSERT INTO system.job_component_values VALUES ( 4,3,'CODE3');
    INSERT INTO system.job_component_values VALUES ( 4,4,'CODE4');
    INSERT INTO system.job_component_values VALUES ( 4,5,'CODE5');
    INSERT INTO system.job_component_values VALUES ( 4,6,'CODE6');
    INSERT INTO system.job_component_values VALUES ( 4,7,'CODE7');
    INSERT INTO system.job_component_values VALUES ( 4,8,'CODE8');
    INSERT INTO system.job_component_values VALUES ( 4,9,'CODE9');
    INSERT INTO system.job_component_values VALUES ( 4,10,'CODE10');
    INSERT INTO system.job_component_values VALUES ( 4,11,'CODE11');
    INSERT INTO system.job_component_values VALUES ( 4,12,'CODE12');
    INSERT INTO system.job_component_values VALUES ( 4,13,'CODE13');
    INSERT INTO system.job_component_values VALUES ( 5,1,'CODE1');
    INSERT INTO system.job_component_values VALUES ( 5,2,'CODE2');
    INSERT INTO system.job_component_values VALUES ( 5,3,'CODE3');
    INSERT INTO system.job_component_values VALUES ( 5,4,'CODE4');
    INSERT INTO system.job_component_values VALUES ( 5,5,'CODE5');
    INSERT INTO system.job_component_values VALUES ( 5,6,'CODE6');
    INSERT INTO system.job_component_values VALUES ( 5,7,'CODE7');
    INSERT INTO system.job_component_values VALUES ( 5,8,'CODE8');
    INSERT INTO system.job_component_values VALUES ( 5,9,'CODE9');
    INSERT INTO system.job_component_values VALUES ( 5,10,'CODE10');
    INSERT INTO system.job_component_values VALUES ( 5,11,'CODE11');
    INSERT INTO system.job_component_values VALUES ( 5,12,'CODE12');
    INSERT INTO system.job_component_values VALUES ( 5,13,'CODE13');
    INSERT INTO system.job_component_values VALUES ( 6,1,'CODE1');
    INSERT INTO system.job_component_values VALUES ( 6,2,'CODE2');
    INSERT INTO system.job_component_values VALUES ( 6,3,'CODE3');
    INSERT INTO system.job_component_values VALUES ( 6,4,'CODE4');
    INSERT INTO system.job_component_values VALUES ( 6,5,'CODE5');
    INSERT INTO system.job_component_values VALUES ( 6,6,'CODE6');
    INSERT INTO system.job_component_values VALUES ( 6,7,'CODE7');
    INSERT INTO system.job_component_values VALUES ( 6,8,'CODE8');
    INSERT INTO system.job_component_values VALUES ( 6,9,'CODE9');
    INSERT INTO system.job_component_values VALUES ( 6,10,'CODE10');
    INSERT INTO system.job_component_values VALUES ( 6,11,'CODE11');
    INSERT INTO system.job_component_values VALUES ( 6,12,'CODE12');
    INSERT INTO system.job_component_values VALUES ( 6,13,'CODE13');
    INSERT INTO system.job_component_values VALUES ( 7,1,'CODE1');
    INSERT INTO system.job_component_values VALUES ( 7,2,'CODE2');
    INSERT INTO system.job_component_values VALUES ( 7,3,'CODE3');
    INSERT INTO system.job_component_values VALUES ( 7,4,'CODE4');
    INSERT INTO system.job_component_values VALUES ( 7,5,'CODE5');
    INSERT INTO system.job_component_values VALUES ( 7,6,'CODE6');
    INSERT INTO system.job_component_values VALUES ( 7,7,'CODE7');
    INSERT INTO system.job_component_values VALUES ( 7,8,'CODE8');
    INSERT INTO system.job_component_values VALUES ( 7,9,'CODE9');
    INSERT INTO system.job_component_values VALUES ( 7,10,'CODE10');
    
    
    COMMIT;