PG之取数组中json串指定字段的值

        项目重构，将数据库从Oracle更换成PG。由于刚接触PG不久，对于PG数据库的字段值，为数组json数据格式的取数，过程中出现了执行SQL取不到数的问题，经过查询资料并调整SQL，可以取到指定的值。

1、json_array_elements()函数

        其作用是将顶层 JSON 数组扩展为一个 JSON 值的集合，json_array_elements(ys_discount_details)去掉了数组，只留下json值。

json_array_elements(ys_discount_details::json) 与 json_array_elements(ys_discount_details)::json输出结果是无差别的，最终输出的同json_array_elements(ys_discount_details)结果一致。

json_array_elements(ys_discount_details)

json_array_elements(ys_discount_details::json)
json_array_elements(ys_discount_details)::json

2、“->”、“->>” 符号区别

         “->”、“->>”从json串中取对应的值，“->”输出格式是json，“->>”输出格式是text。

        “->0” 这里的0是指数组的第一个值，因为数据的特殊性，该字段的数组中只有一个值，且这个值是存在多层嵌套的json串，“->0” 输出的结果是json串，“->0->>”从json串取指定字段的值

json_array_elements(ys_discount_details)->>'activity_id' as activity_id & json_array_elements(ys_discount_details::json)->>'activity_id' as activity_id 同 ys_discount_details->0->>'activity_id' as activity_id 输出结果一致

3、取多层json里面指定字段的值

        从以下三点执行结果无报错可知“ys_discount_details)::json”的“::json”去掉是不影响输出结果的，但是若将 “'marketing_return_info')::json” 的 “::json” 执行会报错（如图二）,原因是“marketing_return_info” 输出的结果为text格式，要将其转换成json格式，才能取到值，否组输出为空，再取下一层也是同理

a. 取第二层json指定字段的值：

(json_array_elements(ys_discount_details)::json->>'marketing_return_info')::json->>'discountType' as discountType

b. 取第三层json指定字段的值： 

(json_array_elements(ys_discount_details)::json->>'marketing_return_info')::json->>'printMap' as printMap

c. 取第四层json指定字段的值：

(json_array_elements(ys_discount_details)->>'marketing_return_info')::json->'printMap'->>'活动运营方' as act_name

 

4、完整的SQL以及查询结果截图 

SELECT 
ys_discount_details, --原本的值
json_array_elements(ys_discount_details), --去掉array后为json串的值
json_array_elements(ys_discount_details)->>'activity_id' as activity_id,
json_array_elements(ys_discount_details::json)->>'activity_id' as activity_id,--从json中取对应的值
json_array_elements(ys_discount_details)::json->>'activity_id' as activity_id, 
ys_discount_details->0->>'activity_id' as activity_id, --直接从数组取json中对应的值
ys_discount_details->0->>'activity_rule_id' as activity_rule_id,
ys_discount_details->0->>'discount_amount' as discount_amount,
ys_discount_details->0->>'marketing_return_info' as marketing_return_info,
json_array_elements(ys_discount_details)::json->>'marketing_return_info' as marketing_return_info,
(json_array_elements(ys_discount_details)::json->>'marketing_return_info')::json->>'discountType' as discountType,
(json_array_elements(ys_discount_details)::json->>'marketing_return_info')::json->>'printMap' as printMap,
(json_array_elements(ys_discount_details)->>'marketing_return_info')::json->'printMap'->>'活动运营方' as act_name,
ys_discount_details->0->>'activity_name' as activity_name
FROM xx where xx='**'

图一

 图二

PS：这里的SQL是根据个人理解并调整后所记录，如果发现有问题，欢迎大家提出建议和指正 。