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 the name column of
Tag: jsonb
Postgresql, retrieve value for specific key from json array
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 Postgres 9.4+ use the function jsonb_array_elements() in a lateral join: You can
PostgreSQL: append elements to jsonb array only if element is unique
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
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 whole
jsonb query with nested objects in an array
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 look
What’s the proper index for querying structures in arrays in Postgres jsonb?
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 that column? Worth noting that it seems I’m
Query for array elements inside JSON type
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”}, {“…