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,"...

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....

What is the equivalent for coalesce in the new jOOQ 3.14 SQL/JSON supporting version (in PostgreSQL)? select coalesce(json_agg(t.*), '[]'::json) from (select 'test' as mycol where 1 = 2) t; The fo...

Here' a dummy data for the jsonb column [ { "name": [ "sun11", "sun12" ], "alignment": "center", "more": "fields" }, { "name": [ "sun12", "sun13" ], "alignment": "center" }, { "name": [ "sun14", "...

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 lim...

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_profi...

I have a table called pins like this: id (int) | pin_codes (jsonb) -------------------------------- 1 | [4000, 5000, 6000] 2 | [8500, 8400, 8600] 3 | [2700, 2300, 2980] Now, I want the row with ...

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...

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?

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, ...