PostGreSql - 提取jsonb数据

本文主要介绍如何在PostGreSql中提取出jsonb类型字段中的某个key的值

参考：https://www.cnblogs.com/mywebnumber/p/5551092.html

一、简单模式

只有一个{}

# rule_config 字段格式

{

  "cardType": 1,

  "sellPrice": null,

  "originPrice": 15

}

# 获取cardType

SELECT

    rule_config -> 'cardType' AS cardType

FROM

    t_free_ride_card_rule_config

WHERE

    title = '测试0211'

只有一个[]

# rule_config字段格式

[

  "zero",

  "one",

  "two"

]

# 获取第一个元素

SELECT

    rule_config ->0  AS cardType

FROM

    t_free_ride_card_rule_config

WHERE

    title = '测试0211'

两个{}并列

# rule_config字段格式

[

  {

    "ruleGuid": "1125720873758932994",

    "mcardType": 1,

    "packStart": "2020-02-14",

  },

  {

    "ruleGuid": "1073055433702576129",

    "mcardType": 1,

    "packExpire": "2020-02-18"

  }

]

# 获取key为ruleGuid 和 ruleGuid 的值

SELECT

    json_array_elements ( rule_config :: json ) -> 'ruleGuid' AS ruleGuid,

    json_array_elements ( rule_config :: json ) -> 'mcardType' AS mcardType

FROM

    t_free_ride_card_rule_config

WHERE

    title = '测试0211'

二、复杂格式

jsonb数据格式如下

[

  {

    "platform": 0,

    "cardPackage": [

      {

        "ruleGuid": "1125720873758932994",

        "mcardType": 1,

        "packStart": "2020-02-14",

        "packExpire": "2020-02-15"

      },

      {

        "ruleGuid": "1073055433702576129",

        "mcardType": 1,

        "packStart": "2020-02-16",

        "packExpire": "2020-02-18"

      }

    ],

    "platformExpire": "2020-02-18"

  }

]

获取相关字段的sql

# 第一种，嵌套sql

SELECT

    bottom :: json ->> 'ruleGuid' AS ruleGuid

FROM

    (

    SELECT

        json_array_elements ( cardPackage ) AS bottom

    FROM

        ( SELECT expire_info :: json -> 'cardPackage' AS cardPackage FROM ( SELECT expire_info :: json -> 0 AS expire_info FROM t_ev_month_card WHERE user_new_id = '1200107139' ) AS A ) AS B

    ) AS F


说明：
第一层sql：SELECT expire_info :: json -> 0 AS expire_info FROM t_ev_month_card WHERE user_new_id = '1200107139' 获取到最外层1个{}的数据，命名为expire_info列
第二层sql：从最外层花括号里expire_info获取到 key = “cardPackage” 的内容 expire_info :: json -> 'cardPackage' ，命名为 cardPackage列
第三层sql：将cardPackage列的内容分为单独的{}
第四层sql：从每个单独的{}中取出ruleGuid字段（见 一、（1）节）

# 第二种，

SELECT (json_array_elements((expire_info -> 0):: json ->'cardPackage'))->'ruleGuid' AS a

FROM t_ev_month_card WHERE user_new_id = '1200107139'

如果数据类型是jsonb，也可以将json替换成jsonb:

SELECT

    bottom :: jsonb -> 'ruleGuid' AS ruleGuid

FROM

    (

    SELECT

        jsonb_array_elements ( cardPackage ) AS bottom

    FROM

        (

        SELECT

            expire_info :: jsonb -> 'cardPackage' AS cardPackage

        FROM

            (

            SELECT

                expire_info :: jsonb -> 0 AS expire_info

            FROM

                t_ev_month_card

            WHERE

                user_new_id = '1200107139'

            ) AS A

        ) AS B

    ) AS F

巴特西

PostGreSql - 提取jsonb数据

最新文章

热门文章