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},…

Postgres jsonb field to array

I was going through the Postgres Jsonb documentation but was unable to find a solution for a small issue I’m having. I’ve got a table : MY_TABLE that has the following columns: User, Name, Data and …

Speed up query on JSONB object field Postgres / indexing a JSONB field

I am trying to speed up queries on an object field in Postgres. The table I am searching has the following structure: page_id: integer lang: varchar(2) images: jsonb The images JSONB field contains …

PostgreSQL-10: query JSONB property with multiple types

Assume a table json_table with a column data (jsonb). A sample value would be {“a”: [{“b”:{“c”: “xxx”, “d”: 1}},{“b”:{“c”: “xxx”, “d”: 2}}]} I used to run SQL queries like the following: SELECT …

How to get specific objects value from JSONB array in PostgreSQL?

I have a column named people and it’s type is JSONB. Here is the sample data (1 row): {“addresses”: [{“street”:”cubuklu”, “valid?”: “true”} {“street”:”beykoz”, “valid?”:”false”} …

How to query Json array in jsonb in Postgresql

I have a jsonb column which have following rows ROW1: [ { “cpe23Uri”: “cpe:2.3:a:sgi:irix:3.55:*:*:*:*:*:*:*”, “active”: true }, { “…

How can I add condition based on select value (JSONB) on PostgreSQL 12?

I want to filter rows that have gold badges. I am using PG12 and I use new path feature of it. To clarify, I have a users_tbl table like this: CREATE TABLE users_tbl ( ID serial NOT NULL PRIMARY …