pgsql中将json字符串转换为结果集
1、建表语句
DROP TABLE IF EXISTS "public"."json_param";
CREATE TABLE "public"."json_param" (
"id" int4 NOT NULL,
"json" text COLLATE "pg_catalog"."default"
);
INSERT INTO "public"."json_param" VALUES (1, '{"name":"xinglei","age":20,"grade":{"math":80,"english":90},"like":[{"label":1,"name":"乒乓球"},{"label":1,"name":"足球"},{"label":1,"name":"篮球球"}]}');
ALTER TABLE "public"."json_param" ADD CONSTRAINT "json_pkey" PRIMARY KEY ("id");
2、json转结果集
SELECT *,cast(one.grade as jsonb) ->> 'math' as math,cast(one.grade as jsonb) ->> 'english' as english,
json_array_elements(one.like::json) ->> 'name' as "likeName"
from (
SELECT json,cast(json as jsonb) ->> 'name' as name,cast(json as jsonb) ->> 'age' as age,
cast(json as jsonb) ->> 'grade' as grade,cast(json as jsonb) ->> 'like' as like from json_param
) one
