Skip to content

Tag: jsonb

JSONB sort aggregation

I found this query that suits my needs thanks to this answer here in order to sort fields of data in a JSON document. (Fake, generated random data) SELECT jsonb_agg(elem) FROM ( SELECT * FROM …

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 the name column of

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