Problem I have a table like this: product tags (jsonb) P001 [{“name”: “LX”,”active”: true}, {“name”: “TX”,”active”: true}] P002 [{“name”: “LX”,”active”: true}] I am trying to query against this table to…
Tag: jsonb
Postgres Nested JSONB Query
I have a JSONB column, data, in the orders table: I’m trying to select “orders where discount_codes contain a code in codes_array”. I could not figure out how to write this query exactly. I read about the [*] operator but am unsure how to use it in this context. This only searches the first …
Assuming there is enough disc space, can I create an index in a live production database without risk of downtime?
In a PostgreSQL database, assuming there is enough disc space, can I create an index in a live production database without risk of downtime? In other words, are there locks or possible crash or data loss possible or something else with the creation of an index. To be more precise, it’s an index on a JSO…
How to extract values from array json column into multiple rows in Postgresql?
How can I extract values from the json arrays in ranges column as multiple rows Postgresq? Expected result: Start End 1 100 101 1000 1001 2000 2001 2002 Answer You can use jsonb_to_recordset function for this : http://www.sqlfiddle.com/#!17/22d62/10
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. T…
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 -> val…
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 …
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 canR…
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 …