Tag: jsonb

Update a field in a JSON column in PostgreSQL

I have a work_item table that has the following schema and a document_type table with the following schema: The data column is a json column that has a Type field. This is a sample column data: I need to update data columns whose data_type column value is DocumentModel and Type field values matches a value in…

Postgresql, retrieve value for specific key from json array

arrays json jsonb postgresql sql

I have Postgres JSONB array of objects, looking like this : This JSONB is a function argument. What is the most efficient way to retrieve skillLevel for skillId = “1”. I’ve tried to play with jsonb_array_elements but everything I’ve done so far is looking really messy. Answer In Postgr…

PostgreSQL: append elements to jsonb array only if element is unique

json jsonb postgresql sql

I created a PostgreSQL (v10.0) table with a jsonb-array column as follows: Then I want add new animals to the first row as follows: However, I want to append only those elements that are not yet in the array. In this case only [chicken, 2]. Answer probably simplest would be:

jsonb LIKE query on nested objects in an array

jsonb pattern-matching postgresql postgresql-9.6 sql

My JSON data looks like this: given a text “foo” I want to return all the tuples that have this substring. But I cannot figure out how to write the query for the same. I followed this related answer but cannot figure out how to do LIKE. This is what I have working right now: Instead of passing the…

jsonb query with nested objects in an array

jsonb postgresql postgresql-9.4 relational-division sql

I’m using PostgreSQL 9.4 with a table teams containing a jsonb column named json. I am looking for a query where I can get all teams which have the Players 3, 4 and 7 in their array of players. The table contains two rows with the following json data: First row: second row: How does the query have to lo…

What’s the proper index for querying structures in arrays in Postgres jsonb?

indexing jsonb postgresql set-returning-functions sql

I’m experimenting with keeping values like the following in a Postgres jsonb field in Postgres 9.4: I’m executing queries like: How would I create an index on that data for queries like the above to utilize? Does this sound reasonable design for a few million rows that each contain ~10 events in t…

Query for array elements inside JSON type

json jsonb lateral postgresql sql

I’m trying to test out the json type in PostgreSQL 9.3. I have a json column called data in a table called reports. The JSON looks something like this: { “objects”: [ {“src”:”foo.png”}, {“…