postgresql对json的支持（tpz）_pgsql插入json内容的语句-优快云博客

1、创建含有json数据类型的表格

CREATE TABLE if not exists public.student
        (
          name varchar,
          user_card varchar,
          age int,
          info jsonb,
          CONSTRAINT student_pkey PRIMARY KEY (user_card)
        );
        insert into public.student values('tom', '1234567890', 11,'{"key1":[1,2,4,["a","b","c"]],"key2":[2,5,7,["a","d","e"]]}');
        insert into public.student values('katty', '1234509890', 20,'{"key1":[8,10,4,["j","n","c"]],"key2":[9,5,19,["a","m","e"]]}');

2、根据条件获取json

select info->'key1'  as info_list from public.student;


//挑选id  和checkresult的json字段的name属性 以别名 check_name和checkresult从表中查询
查询条件 checkresult中的name 为德玛西亚
SELECT ID
    ,
    checkresult :: json -> 'name' AS check_name,
    checkresult 
FROM
    check_result 
WHERE
    ( checkresult :: json -> 'name' ) :: TEXT like '%德玛西亚%';

//在表中添加一个 extra_config  的json字段
ALTER TABLE user ADD COLUMN extra_config json; 

//插入一组数据 
INSERT INTO user (id, extra_config) VALUES(1, '{"name":"张三","age":18,"birthday":"2013-03-03"}')


//查找名字是张三的数据
SELECT id,extra_config::json->'name' as name FROM user WHERE (extra_config::json->'name')::text = '张三'



select a::json#>'{person,name}' as name_json,(a::json#>'{person,name}')::text as name_txt,(a::json#>>'{person,name}')::text as name_textval_only,* from json_array_elements('[{"person":{"name":"李四","age":17,"birthday":"2014-03-15"}},{"person":{"name":"王武","age":27,"birthday":"2013-03-15"}}]') as a

where (a::json#>>'{person,name}')::text = '李四';

(85条消息) PGSQL JSONB或者JSON类型字段常见用法_呆呆槑呆呆的博客-优快云博客_::jsonb