PostgreSql中解析JSON字段和解析TEXT中的JSON字段

刺骨的言语ヽ痛彻心扉 2024-02-19 10:51 10阅读 0赞

### 初始化操作 ###

创建表

CREATE TABLE orders (  "ID" int8 NOT NULL,
                           "info_j" json NOT NULL,
                           "info_t" text NOT NULL
    );

初始化表

INSERT INTO orders("ID", "info_j","info_t") VALUES (1, '{
        "name":"张三","items":{
        "product":"啤酒","qty":6}}','{
        "name":"张三","items":{
        "product":"啤酒","qty":6}}');
    INSERT INTO orders("ID", "info_j","info_t") VALUES (2, '{
        "name":"李四","items":{
        "product":"辣条","qty":8}}','{
        "name":"李四","items":{
        "product":"辣条","qty":8}}');
    INSERT INTO orders("ID", "info_j","info_t") VALUES (3, '{
        "name":"王五","items":{
        "product":"苹果","qty":18}}','{
        "name":"王五","items":{
        "product":"苹果","qty":18}}');
    INSERT INTO orders("ID", "info_j","info_t") VALUES (4, '{
        "name":"赵一","items":{
        "product":"香蕉","qty":20}}','{
        "name":"赵一","items":{
        "product":"香蕉","qty":20}}');

查询表  
![在这里插入图片描述][fa36508d72604f448f6d7354b262b73f.png]

### 解析JSON字段 ###

查询使用->操作符，查询json中所有顾客作为键

SELECT info_j -> 'name' AS customer FROM orders;

![在这里插入图片描述][ab81ffa46bf1433a920839a99ea8aae8.png]  
下面使用->>操作获取所有顾客姓名作为值

SELECT info_j ->> 'name' AS customer FROM orders;

![在这里插入图片描述][115bb56ddce3471aa179847688267a46.png]  
根据json对象的key查询值

SELECT info_j -> 'items' ->> 'product' AS customer FROM orders;

![在这里插入图片描述][a871dc6064f34a70885057695e3009fb.png]

### 解析TEXT中的JSON字段 ###

其实和解析JSON字段一样我们只需要加上::json  
![在这里插入图片描述][d6701a51f1f24d4886aacd31d0f2a66c.png]  
![在这里插入图片描述][e32809c848d24629abb6c2e3670eaeba.png]  
![在这里插入图片描述][d6689de02d9049d781aedee7c4031542.png]

[fa36508d72604f448f6d7354b262b73f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/14312dbe081f4fa0a3ff1ea1e0075265.png
[ab81ffa46bf1433a920839a99ea8aae8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/3f0e91687698409c9f99ed488384cb74.png
[115bb56ddce3471aa179847688267a46.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/0f42d3a93bf34698ad3c60f2f208a87a.png
[a871dc6064f34a70885057695e3009fb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/0a03c0d81c514098984efec65ebeecd9.png
[d6701a51f1f24d4886aacd31d0f2a66c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/6a1534d06e8246618e1749b8f59b14d2.png
[e32809c848d24629abb6c2e3670eaeba.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/c0c8ec164ff2452f964e737d76018264.png
[d6689de02d9049d781aedee7c4031542.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/4458759edbc64a7f81d780d2ec438d59.png