Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to convert postgresql 9.4 jsonb to object without function/server side language

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}

to transform it into:

 {1:[1,2,null,null],2:[3,4,7,null],3:[5,5,null,8]}
like image 902
Kokizzu Avatar asked Nov 28 '14 04:11

Kokizzu


People also ask

How do I query Jsonb data in PostgreSQL?

Querying the JSON documentPostgreSQL has two native operators -> and ->> to query JSON documents. The first operator -> returns a JSON object, while the operator ->> returns text. These operators work on both JSON as well as JSONB columns. There are additional operators available for JSONB columns.

Should I use Jsonb in Postgres?

JSONB supports indexing the JSON data, and is very efficient at parsing and querying the JSON data. In most cases, when you work with JSON in PostgreSQL, you should be using JSONB.

What is the difference between JSON and Jsonb in Postgres?

The json data type stores an exact copy of the input text, which processing functions must reparse on each execution; while jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed.

Is Jsonb faster than JSON?

Json processes input faster than jsonb as there is no conversion involved in this. Jsonb converts the JSON data into the binary form so it has slightly slower input due to the binary conversion overhead.

Does PostgreSQL support JSON data types?

For details on JSON types supported in PostgreSQL, see Section 8.14. 9.16.1. Processing and Creating JSON Data Table 9.44 shows the operators that are available for use with JSON data types (see Section 8.14 ). In addition, the usual comparison operators shown in Table 9.1 are available for jsonb, though not for json.

How to convert PostgreSQL multidimensional array to JSON object?

A PostgreSQL multidimensional array becomes a JSON array of arrays. Line feeds will be added between dimension-1 elements if pretty_bool is true. array_to_json (' { {1,5}, {99,100}}'::int []) [ [1,5], [99,100]] row_to_json (record pretty_bool]) Returns the row as a JSON object.

What is jsonpath in PostgreSQL?

In PostgreSQL, path expressions are implemented as the jsonpath data type and can use any elements described in Section 8.14.7. JSON query functions and operators pass the provided path expression to the path engine for evaluation. If the expression matches the queried JSON data, the corresponding JSON item, or set of items, is returned.

How do I convert SQL data to JSON?

Converts any SQL value to json or jsonb. Arrays and composites are converted recursively to arrays and objects (multidimensional arrays become arrays of arrays in JSON). Otherwise, if there is a cast from the SQL data type to json, the cast function will be used to perform the conversion; [a] otherwise, a scalar JSON value is produced.


2 Answers

create an new data type.

create type fourints as (a int, b int,c int,d int);

then:

SELECT t.id, d.*
FROM   test1 t
     , jsonb_populate_record(null::fourints, t.data) d;
like image 183
Mark Avatar answered Oct 05 '22 09:10

Mark


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);

Then:

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);

Or extract and cast each field individually:

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

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

Related:

  • Query combinations with nested array of records in JSON datatype
like image 30
Erwin Brandstetter Avatar answered Oct 05 '22 09:10

Erwin Brandstetter