Parse JSON array APEX_JSON in PL SQL

Question

I have the following JSON object -

{
  "items": [
    {
      "tableName": "contacts",
      "count": 1,
      "columnNames": [
        "id"
      ],
      "rows": [
        [
          "45"
        ]
      ]
    }
  ],
  "links": [
    {
      "rel": "self",

    },
    {
      "rel": "describedby",

    }
  ]
}

I am trying to extract the value from rows- I need the value 45.

I tried to extract using -

row_id := apex_json.get_number ('rows[%d]', 1);

How I can extract it? Thanks in advance.

Dan McGhan · Accepted Answer · 2020-02-16 17:10:24Z

1

Here's an example:

declare

  l_json_val apex_json.t_values;
  l_clob     clob;
  l_row_val  varchar2(255);

begin

  l_clob := q'-
    {
      "items": [
        {
          "tableName": "contacts",
          "count": 1,
          "columnNames": [
            "id"
          ],
          "rows": [
            [
              "45"
            ]
          ]
        }
      ],
      "links": [
        {
          "rel": "self",

        },
        {
          "rel": "describedby",

        }
      ]
    }
  -';

  apex_json.parse(p_values => l_json_val, p_source => l_clob, p_strict => false); 

  l_row_val := apex_json.get_varchar2(p_path => 'items[1].rows[1][1]', p_values => l_json_val);

  dbms_output.put_line(l_row_val);

end;

Note that the path expressions with apex_json use a 1 base array index, not 0.

answered Feb 16, 2020 at 17:10

Dan McGhan

4,6691 gold badge14 silver badges15 bronze badges

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

Collectives™ on Stack Overflow

Parse JSON array APEX_JSON in PL SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related