0

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?

Brian Robbins
  • 290
  • 3
  • 17
  • This question is all over the place. You have sample data that doesn't match the words you're using to describe it, and then code that doesn't even relate to it. Please clarify what you are asking and provide some information on what you've tried. – James Sumners Feb 12 '15 at 03:56
  • @jsumners Edited for clarity – Brian Robbins Feb 12 '15 at 14:40

1 Answers1

2

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;
James Sumners
  • 14,485
  • 10
  • 59
  • 77