Oracle SQL: Parse JSON in PL/SQL to table or array

Question

I need to parse JSON to a table or array. As this is completely new for me I've used Google for help but something is not working for me :/

My JSON looks like:

{
  "statusCode": 200,
  "isValid": true,
  "errors": [],
  "result": {
    "optimizationData": [
      {
        "name": "out",
        "content": "ID_LIMIT;TIME_STAMP_FROM;DIRECTION;ID_MODEL_CONSTRAINT\n1;202109222200;G;2_7_1_G\n1;202109232200;G;2_3_1_G\n2;202109222200;G;2_3_1_G\n3;202109222200;G;3_3_1_P\n"
      },
      {
        "name": "unit_out",
        "content": "ID_LIMIT;CODE_UNIT;TIME_STAMP_FROM;TIME_STAMP_TO;VARIABLE;VALUE\n1;BEL 2-02;202109222200;202109232200;RelaxationPlus;10\n1;BEL 2-05;202109222200;202109232200;RelaxationPlus;10\n2;WLO 1-01;202109222200;202109232200;RelaxationMinus;10\n"
      }
    ]
  }
}

From above JSON I'd like to create table (or arrays) like below (example expected data for name: "unit_out"):

ID_LIMIT	CODE_UNIT	TIME_STAMP_FROM	TIME_STAMP_TO	VARIABLE	VALUE
1	BEL 2-02	202109222200	202109232200	RelaxationPlus	10
1	BEL 2-05	202109222200	202109232200	RelaxationPlus	10
2	WLO 1-01	202109222200	202109232200	RelaxationMinus	10

So far I've tried to wrote a function to return "content" value, but I'm getting ORA-30625 (method dispatch on null self argument is disallowed)

CREATE OR REPLACE FUNCTION parse_json (json IN VARCHAR2)
  RETURN VARCHAR2 IS
  json_obj_in  JSON_OBJECT_T;
  json_arr     JSON_ARRAY_T;
  json_elem    JSON_ELEMENT_T;
  json_obj     JSON_OBJECT_T;
  name         varchar2(32000);
  content      varchar2(32000);

BEGIN
  json_obj_in := JSON_OBJECT_T.parse(json);

  json_arr := json_obj_in.get_Array('result');

  FOR i IN 0 .. json_arr.get_size - 1 --NVL(json_arr.FIRST, 1) .. NVL(json_arr.LAST, 0)
  LOOP
    json_obj := JSON_OBJECT_T(json_arr.get(i));

    name := json_obj.get_Object('optimizationData').get_string('name');
    content := json_obj.get_Object('optimizationData').get_string('content');

  END LOOP;

  RETURN json_obj_in.to_string;
END;

I'm not sure what's wrong or if my approach to this task is correct.

Can anyone help?

Thanks

Seems like you just missed a step. 'result' is not an array in your JSON, 'optimizationData' is. Use get_object to get the 'result' object first and then use get_array (on that) to get the 'optimizationData' array. Note also that you could consider approaching this entirely differently with JSON_TABLE as per posted answers. — Padders
– Padders, Commented Mar 22, 2022 at 13:01

MT0 · Accepted Answer · 2022-03-22 12:43:34Z

1

From Oracle 12, you can do all the parsing in an SQL query. Your main issue is not the JSON but that you have your data in strings and you will need to split those into lines and then into value and correlate the values with the headers:

SELECT p.*
FROM   (
  SELECT l.lineno,
         kv.key,
         kv.value
  FROM   table_name t
         CROSS APPLY JSON_TABLE(
           t.value,
           '$.result.optimizationData[*]?(@.name == "unit_out")'
           COLUMNS
             content CLOB PATH '$.content'
         ) j
         CROSS JOIN LATERAL (
           SELECT LEVEL AS lineno,
                  REGEXP_SUBSTR(j.content, '.+', 1, 1 ) AS header,
                  REGEXP_SUBSTR(j.content, '.+', 1, LEVEL ) AS line
           FROM   DUAL
           WHERE  LEVEL > 1
           CONNECT BY LEVEL <= REGEXP_COUNT(j.content, '.+')
         ) l
         CROSS JOIN LATERAL (
           SELECT CAST(REGEXP_SUBSTR(header, '[^;]+', 1, LEVEL) AS VARCHAR2(4000))
                    AS key,
                  CAST(REGEXP_SUBSTR(line, '[^;]+', 1, LEVEL) AS VARCHAR2(4000))
                    AS value
           FROM   DUAL
           CONNECT BY LEVEL <= REGEXP_COUNT(header, '[^;]+')
         ) kv
) pt
PIVOT (
  MAX(value)
  FOR key IN (
    'ID_LIMIT'        AS id_limit,
    'CODE_UNIT'       AS code_limit,
    'TIME_STAMP_FROM' AS time_stamp_from,
    'TIME_STAMP_TO'   AS time_stamp_to,
    'VARIABLE'        AS variable,
    'VALUE'           AS value
  )
) p

Which, for the sample data:

CREATE TABLE table_name (value BLOB CHECK (value IS JSON));

INSERT INTO table_name (value) VALUES (
'{
  "statusCode": 200,
  "isValid": true,
  "errors": [],
  "result": {
    "optimizationData": [
      {
        "name": "out",
        "content": "ID_LIMIT;TIME_STAMP_FROM;DIRECTION;ID_MODEL_CONSTRAINT\n1;202109222200;G;2_7_1_G\n1;202109232200;G;2_3_1_G\n2;202109222200;G;2_3_1_G\n3;202109222200;G;3_3_1_P\n"
      },
      {
        "name": "unit_out",
        "content": "ID_LIMIT;CODE_UNIT;TIME_STAMP_FROM;TIME_STAMP_TO;VARIABLE;VALUE\n1;BEL 2-02;202109222200;202109232200;RelaxationPlus;10\n1;BEL 2-05;202109222200;202109232200;RelaxationPlus;10\n2;WLO 1-01;202109222200;202109232200;RelaxationMinus;10\n"
      }
    ]
  }
}'
);

Outputs:

LINENO ID_LIMIT CODE_LIMIT TIME_STAMP_FROM TIME_STAMP_TO VARIABLE VALUE

2 1 BEL 2-02 202109222200 202109232200 RelaxationPlus 10

3 1 BEL 2-05 202109222200 202109232200 RelaxationPlus 10

4 2 WLO 1-01 202109222200 202109232200 RelaxationMinus 10

db<>fiddle here

answered Mar 22, 2022 at 12:43

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MichalAndrzej Over a year ago

Is there a easy way to rewrite it to be dynamic depends on "name" (out/unit_out)? So if I pass name paramter it'll return columns for either 'out' or 'unit_out'. If there is too much effort, that's fine, I can make two procedures

MT0 Over a year ago

@MichalAndrzej SQL (in most dialects, not just Oracle) requires a known set of columns at compile time so you can either have all the columns (for example db<>fiddle) or you can have the columns for each separately (db<>fiddle) but, if you are using plain SQL, you cannot pick only certain columns based on different input parameters.

Tilen · Accepted Answer · 2022-03-22 12:13:05Z

I'm leaving you with an example that worked for me in Oracle 12c:

INSERT INTO table(param_id, name, label, parameterIndex, notNull, parameterTypeName, default_value, lov_query)
      SELECT param_id, name, label, parameterIndex, notNull, parameterTypeName, default_value, lov_query FROM JSON_TABLE(l_json,
      '$.parameters[*]' COLUMNS (
        param_id PATH '$.id', name PATH '$.name', 
        label PATH '$.label', 
        parameterIndex PATH '$.parameterIndex', 
        notNull PATH '$.notNull', 
        parameterTypeName PATH '$.parameterType.parameterTypeName', 
        default_value PATH '$.parameterAttributes.attributeSelectedOption.attributeValue',
        lov_query PATH '$.parameterQuery'
        ) );

I don't know if it's considered a good practice and somebody do correct me if I'm wrong. This worked for me.

Collectives™ on Stack Overflow

Oracle SQL: Parse JSON in PL/SQL to table or array

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related