Get JSON_VALUE with Oracle SQL when multiple nodes share the same name

Question

I have an issue where I have some JSON stored in my oracle database, and I need to extract values from it.

The problem is, there are some fields that are duplicated.

When I try this, it works as there is only one firstname key in the options array:

    SELECT 
    JSON_VALUE('{"increment_id":"2500000043","item_id":"845768","options":[{"firstname":"Kevin"},{"lastname":"Test"}]}', '$.options.firstname') AS value
   FROM DUAL;

Which returns 'Kevin'.

However, when there are two values for the firstname field:

SELECT JSON_VALUE('{"increment_id":"2500000043","item_id":"845768","options":[{"firstname":"Kevin"},{"firstname":"Okay"},{"lastname":"Test"}]}', '$.options.firstname') AS value
  FROM DUAL;

It only returns NULL.

Is there any way to select the first occurence of 'firstname' in this context?

Beda Hammerschmidt · Accepted Answer · 2017-02-16 22:16:46Z

1

JSON_VALUE returns one SQL VALUE from the JSON data (or SQL NULL if the key does not exists).

If you have a collection of values (a JSON array) an you want one specific item of the array you use array subscripts (square brackets) like in JavaScript, for example [2] to select the third item. [0] selects the first item. To get the first array item in your example you have to change the path expression from '$.options.firstname' to '$.options[0].firstname'

answered Feb 16, 2017 at 22:16

Beda Hammerschmidt

4463 silver badges6 bronze badges

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

Comments

Golam Kibria · Accepted Answer · 2021-12-27 10:15:23Z

0

You can follow this query:-

SELECT JSON_VALUE('{
  "increment_id": "2500000043",
  "item_id": "845768",
  "options": [
    {
      "firstname": "Kevin"
    },
    {
      "firstname": "Okay"
    },
    {
      "lastname": "Test"
    }
  ]
}', '$.options[0].firstname') AS value
  FROM DUAL;

answered Dec 27, 2021 at 10:15

Golam Kibria

1268 bronze badges

Comments

nokieng · Accepted Answer · 2023-11-28 10:07:30Z

If there is a multiple node and you would like to search for an object in the array list.

Create Type and Table

CREATE OR REPLACE TYPE "TY_OPTION" IS OBJECT
(
  FIRSTNAME         VARCHAR(255)
)

CREATE OR REPLACE TYPE "TBL_OPTION" AS TABLE OF TY_OPTION

Create query select the array object.

SELECT *
FROM   TABLE (SELECT JSON_VALUE('{"increment_id":"2500000043","item_id":"845768","options":[{"firstname":"Kevin"},{"lastname":"Test"}]}', '$.options'
                                 RETURNING
                                 TBL_OPTION) UDF
              FROM   DUAL)

By combining the technique of type object and type table, we can select the JSON array object into the type table as a temporary table where you could put more conditions on the JSON array object and select the interested / target column as the output of the query.

Collectives™ on Stack Overflow

Get JSON_VALUE with Oracle SQL when multiple nodes share the same name

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related