How to get array index from Oracle JSON column?

Question

Having a JSON like this (I know that JSON doesn't support comments. Used in this case to illustrate the idea):

{
    "people": [
        {                       --//  <-- index 0
            "id": 100,
            "name": "John Doe"
        },
        {                       --//  <-- index 1
            "id": 101,
            "name": "Jane Roe"
        }
    ]
}

We can select values from specific elements in the array doing something like this:

SELECT name
FROM JSON_TABLE(
    '{
        "people": [
            {
                "id": 100,
                "name": "John Doe"
            },
            {
                "id": 101,
                "name": "Jane Roe"
            },
        ]
    }', '$.people[*]' 
    COLUMNS(
        ID      NUMBER      PATH '$.id',
        NAME    VARCHAR2    PATH '$.name'
    )
) info
WHERE info.id = 101

Result:

NAME
--------
Jane Roe

Is there a way to get the element index in the array? Something like:

SELECT array_index    --//  <-- how get the array index of the element found?
FROM JSON_TABLE(
--// ...
) info
WHERE info.id = 101

Result:

ARRAY_INDEX
-----------
1

Is possible to do something like this using JSON support in Oracle 12c?

A JSON is parsed into relation records. Relation records do not have array index. You may assign numbers to the selected records using rownum. — ArtBajji
– ArtBajji, Commented Mar 23, 2018 at 3:38

Alexander Tokarev · Accepted Answer · 2018-04-16 19:46:32Z

8

+50

COLUMNS(
        idx FOR ORDINALITY,
        ID      NUMBER      PATH '$.id',
        NAME    VARCHAR2    PATH '$.name'
)

should work for you

answered Apr 16, 2018 at 19:46

Alexander Tokarev

1,0559 silver badges16 bronze badges

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

2 Comments

Chrᴉz remembers Monica Over a year ago

This adds a rownum to the resultlist, but OP wants the index of the original list, which would be 1 for id 101 and 0 for id 100

Naeel Maqsudov Over a year ago

The answer is correct, (idx-1) always gives desired index. BTW, be careful with for ordinality in 12.1.0.2.0. There is a bug that requires a patch support.oracle.com/rs?type=patch&id=20885778 In 12.2 it works fine

Chrᴉz remembers Monica · Accepted Answer · 2018-04-18 14:41:18Z

As ArtBajji wrote, the rows itself dont have a index. Therefor you need to create a "fake id" that is relatively fixed.

This can be achieved by giving your data a order(order by id) and a index (rownum). Then select from this "modified" table

SELECT name, indx FROM (
SELECT ind.*, rownum "INDX"
FROM JSON_TABLE(
    '{
        "people": [
            {
                "id": 100,
                "name": "John Doe"
            },
            {
                "id": 101,
                "name": "Jane Roe"
            },
        ]
    }', '$.people[*]' 
    COLUMNS(
        ID      NUMBER      PATH '$.id',
        NAME    VARCHAR2    PATH '$.name'
    )
) ind order by id) tab
WHERE tab.id = 101

Yields 1 for id 100 and 2 for id 101.

Be aware that the index of the elements 100 and 101 grow by one if you insert a item with a id < 100.

Collectives™ on Stack Overflow

How to get array index from Oracle JSON column?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related