PL SQL Apex_JSON - Parse element name

Question

I am trying to get the value from the following array -

{
   "list:" [
     {
       "User.name":"AAA"
     },
     {
       "User.Name":"BBB"
     }
  ]  
}

I tried to get the Name value but I got null -

for i in 1..Apex_Json.get_count('list') loop
     l_name := Apex_Json.Get_varchar2('list[%d].User.Name', i);
end loop;

How can I get the name value?

Answer 1

"list" is json array of elements. AFAIK you can't access an element directly but you can loop through the array to check if an element with a specific name exists. Here is an example:

DECLARE
  l_json_text VARCHAR2(4000);
  l_json_values    apex_json.t_values;
  l_name      VARCHAR2(4000);
BEGIN
l_json_text := q'!
{
   "list": [
     {
       "User.name":"AAA"
     },
     {
       "User.Name":"BBB"
     }
  ]  
}
!';

  apex_json.parse(
    p_values => l_json_values,
    p_source => l_json_text
  );
  
  -- get nr of elements in the array and loop through it.    
  FOR r IN 1 .. APEX_JSON.get_count(p_path => 'list', p_values => l_json_values) LOOP
    -- get the value for User.Name for this array index. 
    -- If it is doesn't exist it will be NULL
    l_name := apex_json.get_varchar2(p_path => 'list[%d]."User.name"', p0 => r, p_values => l_json_values);
    IF l_name IS NOT NULL THEN
      DBMS_OUTPUT.put_line('"User.name":'||l_name);
    END IF;
  END LOOP;
END;
/

Note that there is a typo in your question json: "list:" [ should be "list": [