Using a variable value in json_table

Question

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?

Barbaros Özhan · Accepted Answer · 2019-10-24 07:22:55Z

1

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

answered Oct 24, 2019 at 7:22

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

Collectives™ on Stack Overflow

Using a variable value in json_table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related