CODEWITHSUNDEEP
jsonoracle-databaseoracle12c
Rajshree Rai
Rajshree Rai

Reputation: 642

Using a variable value in json_table

I have the following json:

{
    "title": "title",
    "description": "description",
    "references": [{
        "reference_id": 181
    },
{
        "reference_id": 182
    }]
}

I want to insert the data of references array in a table xyz which contains the following columns: buffer_id and reference_id

INSERT INTO xyz (
        buffer_id,
        reference_id
    )
        SELECT
            l_buffer_id,
            reference_id
        FROM
            dual,
            JSON_TABLE ( data, '$.references[*]'
                    COLUMNS (
                        l_buffer_id ,
                        reference_id NUMBER PATH '$.reference_id'
                    )
                );

Here, l_buffer_id is a variable where my value is stored. I tried the above method to insert data into xyz. However it is not working. Is there any way to get this to work?

Upvotes: 2

Views: 814

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65288

You can use such a mechanism :

INSERT INTO xyz (
        buffer_id,
        reference_id
    )
WITH tab(data) AS
(
 SELECT '{"title": "title",
    "description": "description",
    "references": [{
        "reference_id": 181
    },
{
        "reference_id": 182
    }]
}' FROM dual
)
SELECT row_number() over (order by reference_id) as l_buffer_id,
       reference_id
  FROM tab,
       json_table(data, '$'
               COLUMNS (NESTED PATH '$."references"[*]'
                   COLUMNS (reference_id NUMBER PATH '$."reference_id"')));

Demo

Upvotes: 1

Related Questions