Select multiple values from a json array in plsql

Question

I want to select value from a json array. Eg: The data is in table Data in column name json_col

{
    "ABlock": {
        "fruits1": [{
            "Frt1": "Apple",
            "Clr1": "Red",
            "Qty1": "14"
        }, {
            "Frt1": "Grapes",
            "Clr1": "Black",
            "Qty1": "7"
        }],
        "fruits2": [{
            "Frt2": "Pear",
            "Clr2": "Green",
            "Qty2": "9"
        }, {
            "Frt2": "Lemon",
            "Clr2": "Yellow",
            "Qty2": "5"
        }]
    }
}

Here I want to select Qty1&Qty2. The code I tried to select just Qty1 is

Select json_value(json_col, '$.ABlock.fruits1[0].Qty1) + ',' + json_value(json_col, '$.ABlock.fruits1[1].Qty1) as qty 
  from Data;

But I'm getting error as 'invalid number'

The output I should get is: 14 7.

Koen Lostrie · Accepted Answer · 2022-01-21 14:20:29Z

1

Assuming this is oracle, the concatenation symbol is "||", not "+". The invalid number is because you are trying to sum the values "14", "," and "7" which comes down to:

SELECT 14 + ',' + 7 from dual;

ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

This works:

with json_doc AS 
(SELECT
'{
    "ABlock": {
        "fruits1": [{
            "Frt1": "Apple",
            "Clr1": "Red",
            "Qty1": "14"
        }, {
            "Frt1": "Grapes",
            "Clr1": "Black",
            "Qty1": "7"
        }],
        "fruits2": [{
            "Frt2": "Pear",
            "Clr2": "Green",
            "Qty2": "9"
        }, {
            "Frt2": "Lemon",
            "Clr2": "Yellow",
            "Qty2": "5"
        }]
    }
}' AS json_col FROM dual
)
SELECT
json_value(json_col, '$.ABlock.fruits1[0].Qty1') ||' '||
json_value(json_col, '$.ABlock.fruits1[1].Qty1') 
FROM json_doc;

14 7

Replace the ||' '|| with ||','|| to get 14,7

edited Jan 21, 2022 at 14:20

answered Jan 21, 2022 at 14:15

Koen Lostrie

19.5k2 gold badges16 silver badges25 bronze badges

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

1 Comment

SREE LAKSHMI Over a year ago

Yeah it worked. Thank you.

Collectives™ on Stack Overflow

Select multiple values from a json array in plsql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related