如何在 postgresql 表中使用以下 json 格式更新表 [英] how to update table using following json format in postgresql table

问题描述

这是给 postgresql 函数的参数格式.如何读取此 json 值并在 tbl_product_info 表中存储/更新多个对象.或者如何使用for循环来更新一个表中的这些对象数组

This the format of parameter given to postgresql function. how to read this json value and store/update more than one object in tbl_product_info table. or how to use for loop to update these array of objects in one table

{ 
    "tbl_product_info": [
        {
            "customer_id": 1001,
            "product_name": "product1",
             "modified_on": "1961-06-16 00:00:00"
        },
        {
            "customer_id": 1000,
            "product_name": "product2",
            "modified_on": "1961-06-16 00:00:00"
        }
    ]
}

我的postgresql表结构

my table structure of postgresql

CREATE TABLE tbl_product_info(
  customer_id serial PRIMARY KEY,
  modified_on timestamp,
  product_name varchar(50),)

tbl_product_info 表名

推荐答案

你的主键被定义为 serial 所以我不清楚你是否想用来自JSON 输入值，或者如果您想忽略 JSON 中的 ID 值(我还发现一个名为 customer_id 作为表的主键 product_info 非常令人困惑)

Your primary key is defined as a serial so it's unclear to me if you want to bypass the ID generation with the values from the JSON input value or if you want to ignore ID values from the JSON (I also find a named customer_id as the primary key of a table product_info extremely confusing)

如果你想绕过serial，你可以使用这样的函数来插入行

If you want to bypass the serial, you can use a function like this to insert the rows

create function insert_products(p_data json)
  returns void
as
$$
  insert into tbl_product_info (customer_id, modified_on, product_name)
  select (t.cust ->> 'customer_id')::int, 
         (t.cust ->> 'modified_on')::timestamp,
         t.cust ->> 'product_name'
  from json_array_elements(p_data -> 'tbl_product_info') as t(cust)
$$
language sql;

然后像这样使用它:

select *
from insert_products('{ 
    "tbl_product_info": [
        {
            "customer_id": 1000,
            "product_name": "product1",
             "modified_on": "1961-06-16 00:00:00"
        },
        {
            "customer_id": 1000,
            "product_name": "product2",
            "modified_on": "1961-06-16 00:00:00"
        }
    ]
}');

在线示例

如果不想绕过序列号，而忽略 JSON 中的 ID，请将其更改为:

If you don't want to bypass the serial, and ignore the IDs in the JSON, change it to:

create function insert_products(p_data json)
  returns table (customer_id int)
as
$$
  insert into tbl_product_info (modified_on, product_name)
  select (t.cust ->> 'modified_on')::timestamp,
         t.cust ->> 'product_name'
  from json_array_elements(p_data -> 'tbl_product_info') as t(cust)
  returning customer_id;
$$
language sql;

这篇关于如何在 postgresql 表中使用以下 json 格式更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！