Select multiple values from a json array in plsql
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.
Solution 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