Populate postgres table with json array + separate variables

Question

Consider the table:

CREATE TABLE watermelon
(
    order_id integer,
    sort text,
    size integer,
    juiciness integer,
    sender text,
);

I have an incoming json looking like this:

{
    "order_id": 500,
    "items": [
        {
            "sort": "regularMidterranian",
            "size": 6,
            "juiciness": 85
        },
        {
            "sort": "yellowMexican",
            "size": 4,
            "juiciness": 90
        }
    ],
    "sender": "SantaClara"
}

Is there a way to neatly insert this json into the table in one query, thus creating 2 entries with all fields filled?

Or is it simpler to just modify the json beforehand, extracting JArray adding order_id & sender to all items and then using regular json_populate_record?

Mike Organek · Accepted Answer · 2020-06-27 14:51:38Z

Use the JSON types in postgresql.

https://www.postgresql.org/docs/9.5/functions-json.html

You can substitute the literal JSON doc in the incoming CTE with a regular text bind parameter in your C#.

=# truncate table watermelon ;
TRUNCATE TABLE

=# with incoming as (
  select '{
    "order_id": 500,
    "items": [
        {
            "sort": "regularMidterranian",
            "size": 6,
            "juiciness": 85
        },
        {
            "sort": "yellowMexican",
            "size": 4,
            "juiciness": 90
        }
    ],
    "sender": "SantaClara"
  }'::jsonb as indata
), do_unnest as (
  select (indata->>'order_id')::int as order_id, 
         indata->>'sender' as sender,
         jsonb_array_elements(indata->'items') as item
    from incoming
)
insert into watermelon (order_id, sort, size, juiciness, sender)
select order_id, 
       item->>'sort' as sort, 
       (item->>'size')::int as size,
       (item->>'juiciness')::int as juiciness,
       sender
  from do_unnest;
INSERT 0 2

=# select * from watermelon;

 order_id |        sort         | size | juiciness |   sender   
----------+---------------------+------+-----------+------------
      500 | regularMidterranian |    6 |        85 | SantaClara
      500 | yellowMexican       |    4 |        90 | SantaClara
(2 rows)

Collectives™ on Stack Overflow

Populate postgres table with json array + separate variables

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