CODEWITHSUNDEEP
oracle11gpljson
Brian Robbins
Brian Robbins

Reputation: 286

Need to parse a JSON request in pl/sql

I have recently installed the PL/JSON package and need a bit of help using it. I am getting a request (POST) and I need to parse it out to compare against my data. the request will come formatted like this:

{
  "value1": "ABC123",
  "list1": [
    "1",
    "2"
  ]
}

The request validates that the specified value1 has at least one valid value2 for the specified list1 values.

We have a table with a column matching value1, value2, and list1 values in our database. I am using a stored procedure with a cursor to get these values like this:

cursor valid_list1_values is
select list1_values
from myTable
where value1 = ;

I'm stuck on the where clause. This procedure used to pass in two variables instead of the JSON, and I used one of those to narrow the cursor result set.

How do I parse out the JSON object and nested JSON list to get the Value1 to restrict my cursor?

Upvotes: 0

Views: 6087

Answers (1)

James Sumners
James Sumners

Reputation: 14783

First, you need to parse the JSON string into a JSON object. Then you can use the JSON object methods to access the data:

json in_data := json('{"value1":"ABC123", "list1":[1,2]}');
varchar2 json_value1 := in_data.get_string('value1');

At which point you can use the value in your cursor:

cursor valid_list1_values is
select list1_values
from myTable
where value1 = json_value1;

Upvotes: 2

Related Questions