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]}
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.
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.
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.
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.
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.
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.
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.
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.
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;
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:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With