JSON support for PostgreSQL is added in 9.2
Related documentations:
jsontypejsonbtype (will be added in 9.4)- JSON functions
Questions tagged [postgresql-json]
94 questions
487
votes
9 answers
Explanation of JSONB introduced by PostgreSQL
PostgreSQL just introduced JSONB in version 9.4, and it's already trending on hacker news. How is it different from Hstore and JSON previously present in PostgreSQL?
What are its advantages and limitations and when should someone consider using it?
Peeyush
- 6,144
- 5
- 23
- 37
370
votes
24 answers
How do I modify fields inside the new PostgreSQL JSON datatype?
With postgresql 9.3 I can SELECT specific fields of a JSON data type, but how do you modify them using UPDATE? I can't find any examples of this in the postgresql documentation, or anywhere online. I have tried the obvious:
postgres=# create table…
user9645
- 6,286
- 6
- 29
- 43
134
votes
13 answers
PostgreSQL: Remove attribute from JSON column
I need to remove some attributes from a json type column.
The Table:
CREATE TABLE my_table( id VARCHAR(80), data json);
INSERT INTO my_table (id, data) VALUES (
'A',
'{"attrA":1,"attrB":true,"attrC":["a", "b", "c"]}'
);
Now, I need to remove…
sja
- 2,064
- 2
- 18
- 22
47
votes
4 answers
Distinct on Postgresql JSON data column
Trying to do distinct on a mode with rails.
2.1.1 :450 > u.profiles.select("profiles.*").distinct
Profile Load (0.9ms) SELECT DISTINCT profiles.* FROM "profiles" INNER JOIN "integration_profiles" ON "profiles"."id" =…
Mohamed El Mahallawy
- 13,024
- 13
- 52
- 84
38
votes
3 answers
JSON Schema validation in PostgreSQL?
I can't find any information about JSON schema validation in PostgreSQL, is there any way to implement JSON Schema validation on PostgreSQL JSON data type?
khorvat
- 1,630
- 2
- 20
- 31
8
votes
1 answer
Multiple ways to create index on a json field's nested property in PostgreSQL 9.3
In PostgreSQL 9.3, there are multiple ways to build an expression, which points to a json field's nested property:
data->'foo'->>'bar'
data#>>'{foo,bar}'
json_extract_path_text(data, 'foo', 'bar')
Therefore PostgreSQL only use these indexes, if the…
pozs
- 34,608
- 5
- 57
- 63
7
votes
3 answers
Postgres recursive query with row_to_json
I've got a table in postgres 9.3.5 that looks like this:
CREATE TABLE customer_area_node
(
id bigserial NOT NULL,
customer_id integer NOT NULL,
parent_id bigint,
name text,
description text,
CONSTRAINT customer_area_node_pkey PRIMARY…
Ronnyek
- 482
- 5
- 16
6
votes
2 answers
Postgresql get keys from array of objects in JSONB field
Here' a dummy data for the jsonb column
[ { "name": [ "sun11", "sun12" ], "alignment": "center", "more": "fields" }, { "name": [ "sun12", "sun13" ], "alignment": "center" }, { "name": [ "sun14", "sun15" ] }]
I want to fetch all the name keys value…
Jitendra
- 584
- 1
- 10
- 28
5
votes
0 answers
How save Inheritance class list as json in postgresql with jpa?
For saving a list with json format in postgresql I use com.vladmihalcea:hibernate-types-52 in Spring boot 2 as follow:
@Entity
@TypeDefs({
@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
})
public class Profile {
...
…
Morteza Malvandi
- 1,656
- 7
- 30
- 73
5
votes
1 answer
ARRAY_AGG interrupted by GROUP BY when trying to ORDER BY timestamps
I have prepared an SQL Fiddle for my problem -
Given the following table:
CREATE TABLE chat(
gid integer, /* game id */
uid integer, /* user id */
created timestamptz,
msg text
);
filled with the following test…
Alexander Farber
- 21,519
- 75
- 241
- 416
4
votes
1 answer
JSON Where Clause on array of values with Query Builder
Here is a JSON value in a data column of a things table:
{a: [{b: 1}, {b: 2}]}
I can get all things containing a b that is equals to 1 with a raw query like this:
select * from things where data @> '{ "a": [{"b": 1}] }';
I know we can run query…
rap-2-h
- 30,204
- 37
- 167
- 263
4
votes
2 answers
Filter jsonb results in subselect
I'm building a hierarchical JSON result from several tables. These are just examples but should be sufficient for the purpose of this demonstration to get the idea:
CREATE TABLE book (
id INTEGER PRIMARY KEY NOT NULL,
data JSONB
);
CREATE…
dbf
- 3,278
- 1
- 24
- 34
4
votes
1 answer
PostgreSQL. json_object_agg() returns text string instead of json object
As it is written here, json_object_agg(name, value) return type is json. Meanwhile, if I return the value returned by json_object_agg() from a stored procedure:
CREATE OR REPLACE FUNCTION _getlocales()
RETURNS json AS
$BODY$DECLARE
var…
Alexandr Lazarev
- 12,554
- 4
- 38
- 47
3
votes
0 answers
How do I dynamically construct a sql/json path string
I'm trying to construct a json/sql path query dynamically inside a procedure.
profile @? '$.timezone ? (@ like_regex "auck" flag "i")';
In the above I want timezone and auck to be constructed dynamically.
So far I have the…
Tim
- 4,471
- 5
- 36
- 42
3
votes
1 answer
JSON vs TEXT field to keep a list of values in Postgres
What is the best way to keep the following on Postgres?
[{'property':'foo1', 'val': "foo_val"}, {'property': 'foo2', 'val': "foo_val2"}]
JSON or Text?
From what I understand JSON is good for nested structure(using ->>). But in this case, its a flat…
suprita shankar
- 1,554
- 2
- 16
- 47