Postgresql使用JSONArray做IN操作等

/*插入数据*/
INSERT INTO "public"."afs_customer" ("customer_id", "customer_account",  "service_list") VALUES ('1', '6TCM6ih3T8vn0vJk', '[1, 2, 3]');

/*按jsonArray查询数据，结果为数据集*/
SELECT
	*
FROM
	afs_service
WHERE
	service_id IN (
		SELECT
			json_array_elements_text (
				(
					SELECT
						service_list
					FROM
						afs_customer
					WHERE
						customer_id = 6
				) :: json
			) :: BIGINT
	)

/*聚合数据，结果为拼接的字符串*/
SELECT
	string_agg (NAME,',')
FROM
	afs_service
WHERE
	available = '1'
AND service_id IN (
	SELECT
		json_array_elements_text (
			(
				SELECT
					service_list
				FROM
					afs_customer
				WHERE
					customer_id = 2
			) :: json
		) :: BIGINT
)

/*JSONArray做left操作*/
select
    s.*,
    (case when c.customer_id is null then 0 else 1 end) as isChoose
    from afs_service s
    left join afs_customer c on c.service_list @> to_jsonb(s.service_id) and c.customer_id = 2

自己做的淘宝哦京东优惠券搜索助手，自动回复个人号，欢迎关注体验