Querying JSON data field from Oracle table-CodePudding

ORACLE: There is a field in my "TESTREPAIR" table where one column "DETAILS" stores the following JSON.

{
    "repairActions": [
        {
            "symptom": 524,
            "defect": "Defective Component",
            "action": "Reflow"
        },
        {
            "symptom": 506,
            "defect": "Cosmetic",
            "action": "Repaired Component"
        },
        {
            "symptom": 509,
            "defect": "Defective Component",
            "action": "Swapped"
        }
    ]
}

I am using the SELECT statement to just query the symptoms from the above JSON data field.

Like this.

SELECT
    r.details.repairactions.symptom[0],
    r.details.repairactions.symptom[1],
    r.details.repairactions.symptom[2]
FROM
    testrepair r;

The above query is returning null. My desired output is

symptom1: 524
symptom2: 506
symptom3: 509

CodePudding user response：

You want to put the array index in the correct place and use quoted identifiers that match the case of the JSON:

SELECT r.details."repairActions"[0]."symptom" AS symptom1,
       r.details."repairActions"[1]."symptom" AS symptom2,
       r.details."repairActions"[2]."symptom" AS symptom3
FROM   testrepair r;

Which, for the sample data:

CREATE TABLE testrepair (details JSON);

INSERT INTO testrepair (details)
VALUES ('{
    "repairActions": [
        {
            "symptom": 524,
            "defect": "Defective Component",
            "action": "Reflow"
        },
        {
            "symptom": 506,
            "defect": "Cosmetic",
            "action": "Repaired Component"
        },
        {
            "symptom": 509,
            "defect": "Defective Component",
            "action": "Swapped"
        }
    ]
}');

Outputs:

SYMPTOM1 SYMPTOM2 SYMPTOM3

524 506 509

SYMPTOM1	SYMPTOM2	SYMPTOM3
524	506	509

db<>fiddle here