python (12.9k questions)

javascript (9.2k questions)

reactjs (4.7k questions)

java (4.2k questions)

java (4.2k questions)

c# (3.5k questions)

c# (3.5k questions)

html (3.3k questions)

Questions - jsonb

PostgreSQL aggregate over json arrays

I have seen a lot of references to using json_array_elements on extracting the elements of a JSON array. However, this appears to only work on exactly 1 array. If I use this in a generic query, I get ...
test-img

Aserian

arrays

json

postgresql

aggregate-functions

jsonb

Votes: 0

Answers: 1

Latest Answer

Use the function for orders->'items' to flatten the data: select elem->>'name' as name, (elem->>'price')::numeric as price from my_table cross join jsonb_array_elements(orders->'item...
test-img

klin

Postgresql: How to search jsonb for LIKE value

I want to see if a string is in the values anywhere within a jsonb. Not interested in the keys, just values. Something like select the_reults from my_database where jsonb->>'*' L...
test-img

David Pesetsky

sql

postgresql

jsonb

Votes: 0

Answers: 1

Latest Answer

You can try something like this : select the_results from my_database where jsonb_path_exists (jsonb, '$.** ? (exists(@ like_regex "123456"))') group by whatever orde...
test-img

Edouard

Expanding jsob array in PostgreSQL to produce analytics

Imagine that we have the following table using jsonb on PostgreSQL: create table writer ( "firstName" varchar, "surName" varchar, books jsonb ); And the following da...
test-img

glarkou

sql

postgresql

jsonb

Votes: 0

Answers: 1

Latest Answer

You can use jsonb_array_elements() to get one row per array element: select w."firstName", w."surName", b.book ->> 'name' as book_name from writer w cross join jsonb_array_...
test-img

a_horse_with_no_name

postgres 11.6 - Creating array of JSON Objects from JSON array

I have the following schema here: http://sqlfiddle.com/#!17/5c73a/1 I want to create a query where the results will be something like this: id | tags _________________________________ 1. ...
test-img

Pedro Dias

postgresql

aggregate-functions

jsonb

Votes: 0

Answers: 1

Latest Answer

You need to unnest the array and then aggregate it back: select t.id, jsonb_agg(jsonb_build_object('id', 'id', 'title', tg.title)) from things t cross join jsonb_array_elements(tags) as tg(title) gr...
test-img

a_horse_with_no_name

Posts

Questions

Blogs

Jobs