如何将PostgreSQL 9.4 jsonb转换为不带功能/服务器端语言的对象 [英] How to convert postgresql 9.4 jsonb to object without function/server side language

问题描述

是否可以在不创建函数且不使用任何服务器端编程语言的情况下转换postgresql 9.4 jsonb数据?

Is it possible to transform postgresql 9.4 jsonb data without creating function and without using any server side programming language?

CREATE TABLE test (id SERIAL PRIMARY KEY,data JSONB);
INSERT INTO test(data) VALUES('{"a":1,"b":2}');
INSERT INTO test(data) VALUES('{"a":3,"b":4,"c":7}');
INSERT INTO test(data) VALUES('{"a":5,"b":5,"d":8}');
SELECT * FROM test;
 id |           data       
----+-------------------------
  1 | {"a": 1, "b": 2}
  2 | {"a": 3, "b": 4, "c": 7}
  3 | {"a": 5, "b": 5, "d": 8}

将其转换为:

 {1:[1,2,null,null],2:[3,4,7,null],3:[5,5,null,8]}

推荐答案

使用

Use jsonb_populate_record() (or json_populate_record() for json) with a well known row type as target. You can use a temp table to register a type for ad-hoc use (if you can't use an existing table or custom composite type):

CREATE TEMP TABLE obj(a int, b int, c int, d int);

然后:

SELECT t.id, d.*
FROM   test t
     , jsonb_populate_record(null::obj, t.data) d;

或使用

Or use jsonb_to_record() (or json_to_record() for json) and provide a column definition list with the call:

SELECT t.id, d.*
FROM   test t
     , jsonb_to_record(t.data) d(a int, b int, c int, d int);

或分别提取并投射每个字段:

SELECT id, (data->>'a')::int AS a, (data->>'b')::int AS b
         , (data->>'c')::int AS c, (data->>'d')::int AS d
FROM   test;

这三个都对json和jsonb都起作用.只需使用相应的功能变体即可.

All three work for json and jsonb alike. Just use the respective function variant.

相关:

• 使用JSON数据类型的记录嵌套数组的查询组合

这篇关于如何将PostgreSQL 9.4 jsonb转换为不带功能/服务器端语言的对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！