PostGreSql - 提取jsonb数据

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

简单模式

只有一个 { }

# 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

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

# 第一种，嵌套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