如何插入json字段值Postgresql 14

Question

我正试图在Postgres表中插入一些数据。有一列包含一些json数据。但是每次我尝试插入它都会显示出一些错误

以下是我的查询

INSERT INTO settings.tbl_settings 
    (sin_product_id, 
    vhr_sys_module_name, 
    vhr_grouping_name,
    vhr_settings_sys_name, 
    vhr_label,
    vhr_value, 
    arj_select_items,
    txt_remarks,
    vhr_widget_type,
    sin_settings_category, 
    int_sys_action_id, 
    fk_created_user_id, 
    dtm_created)
VALUES
    (1, 
    'XO_PURCHASE', 
    'XO Purchase',
    'NEED_XO_PURCHASE_SUPPLIER_SIDE_POSTING', 
    'Need XO Purchase Supplier Side Posting', 
    'NO_POSTING', 
    '[{"strSysName":"NO_POSTING","strLabel":"No Posting"} ,{"strSysName":"POSTING","strLabel":"Posting"}]'::JSONB[],
    '',
    'SELECTBOX',
    2, 
    0, 
    1, 
    TO_TIMESTAMP('27-08-2022 17.24.34', 'DD/MM/YYYY HH24:MI:SS')
    );

它返回给定的错误

ERROR:  malformed array literal: "[{"strSysName":"NO_POSTING","strLabel":"No Posting"} ,{"strSysName":"POSTING","strLabel":"Posting"}]"
LINE 27:                             '[{"strSysName":"NO_POSTING","st...
                                     ^
DETAIL:  "[" must introduce explicitly-specified array dimensions.
SQL state: 22P02
Character: 1024

Answer 1

您的错误是，在执行插入操作时，可以将字符串转换为jsonb数组(::JSONB[])，但只需要jsonb，因为您的arj_select_items列的类型很可能被定义为jsonb。

[医]小提琴中的演示