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 ) );
job_component_values_sql.txt (5.86 KB)