I got 2 tables; domains and events. Im trying to create a query that returns a list of distinct domains that is ordered by the oldest events (for that domain) with nulls first and distinct domain. Basically this query will do the job: But the output is not distinct on ‘domain’. When using ‘distinct on’, it gives the wrong output
Tag: jsonb
Format JSONB column by taking the text value from same jsonb coulmn
CREATE TABLE test(id serial, data jsonb); INSERT INTO test(data) values (‘dummydata-got-uploaded’); I need to correct the jsonb column value with below query. update test set data={“addDet”: data }::jsonb where id =1; ERROR: syntax error at or near “{” LINE 1: update test set data={“addDet”: data… Expected: id | data 1 | {“addDet”: ‘dummydata-got-uploaded’ } ` Thanks in advance. Answer you
Correct value of a json data saved using JPA, spring and kotlin Map
I have a jsonb column in a postgres database where I store a bunch of key/value(1 level) into a column mappped to a a Map<String, Any). The values are supposed to be a String but in code I’m accepting anything. All saved values was passed as ByteArray(byte[]) and it’s stored without problems. The stored value is transformed to a String
Conditional update with jsonb_set()
I have a table in a Postgres 11.3 database with a jsonb column. Trying to update all objects inside a nested array name “iProps”. If the path {iProps -> value -> rules -> ao -> sc} is an object, then the path should be updated from an object to a string with the value {iProps -> value -> rules ->
postgresql jsonb case insensitive query with index
I was looking for a way to make a case insensitive query, and I found it here (postgresql jsonb case insensitive query), more precisely with a query like this : select … where upper(data::text)::jsonb @> upper(‘[{“city”:”New York”}]’)::jsonb However, I can’t seem to find enough information about how to create an index to be used by such a query. works perfectly
how to use wildcard for a column jsonb type
I have a table (named profile) in my Postgres database, which has 3 columns: ID, Day, Ftfm_profile of type jsonb, I tried to extract the row where the profile name (ftfm_profile->’name’) begins with ‘LFBB’ ( sql: LFBB%) using the wildcard as following: the expected result: I can’t seem to find the solution, thanks for your help Answer One option unnests
Postgresql Convert SQL XML Coding to SQL JSON Coding
How to convert XML SQL Coding to JSON SQL Coding. Example: SELECT XMLELEMENT(NAME “ORDER”, XMLFOREST(PURCHASE_ORDER AS OD_NO)) AS “XMLELEMENT” FROM TBL_SALES Now how to convert …
How to use LOWER() on elements of a jsonb column in PostgreSQL?
I have a PostgreSQL table like this one: Table t id | keys (jsonb) —+—————- 1 | [“Key1”, “Key2”] My goal is to query this table to find out if one of the keys of a …
PostgreSQL Generated Column from a JSONB column with nested values
I have following table with a JSONB column: CREATE TABLE movies ( contributors JSONB ); The data in the column looks like this: INSERT INTO movies (contributors) VALUES(‘[ {“last_name”: “…
How to aggregate json fields when using GROUP BY clause in postgres?
I have the following table structure in my Postgres DB (v12.0) id | pieces | item_id | material_detail —|——–|———|—————– 1 | 10 | 2 | [{“material_id”:1,”pieces”:10},…