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 get a list of products with tags that are the same. I found the following to be insufficient for my query since it will match all products with at least
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 element of discount_codes: This only searches
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 JSONB sub-property in a 1Gb
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. 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 …