Questions tagged [jsonb]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
0 answers
13 views

How to create index for jsonb key existence?

Currently I'm doing this: CREATE INDEX bla ON table1((LENGTH(data->>'programs') > 0)) for the query: EXPLAIN ANALYZE SELECT p AS prog FROM Clients c, jsonb_array_elements(c.data->'...
user avatar
  • 1,219
0 votes
2 answers
13 views

How to make unstructured array of object to row?

c.data is jsonb object, like this: {programs:[{...},{...}]} c.data->'programs' is jsonb array, like this [{a:1},{b:2},{c:3,d:4}] or null Already tried this: SELECT p FROM Clients c , ...
user avatar
  • 1,219
-1 votes
0 answers
27 views

Insert Array of Objects using jsonb_array_elements [closed]

I have an array of json Objects in following format: [ { product: { id: 24, raisedby: 'abc@testcom' } }, { product: { id: 12, raisedby: 'xyz@...
user avatar
  • 1
1 vote
1 answer
25 views

extracting ordered list of spatial points from JSON into PostgreSQL array

i have JSONB data in PostgreSQL that looks like the following: {"datasetGPolygon": [ { "datasetGPolygonOuterGRing": { "gRingPoint": [ { &...
user avatar
1 vote
1 answer
22 views

Keys naming in jsonb_agg postgres function

I have sql: select jsonb_agg((u.id, u.name)) as user_data from users u where u.id = '1'; and result is: [ { "f1": "1", "f2": "Robert Miles" } ] but ...
user avatar
  • 111
0 votes
0 answers
21 views

Join two jsonb on keys and output values

The example setup: CREATE TABLE configs ( device_id int PRIMARY KEY, elements jsonb not null -- more columns omitted ); INSERT INTO configs (device_id, elements) VALUES (1, '{"00": ...
user avatar
1 vote
1 answer
28 views

How to construct a jsonb containing a number?

Is there a way to construct a jsonb with only a numeric value in it? For example, SELECT pg_typeof(('{"a":1}'::jsonb) -> 'a'); indicates that ('{"a":1}'::jsonb) -> 'a' has ...
user avatar
  • 2,814
0 votes
0 answers
21 views

Quickly Parsing Distinct Elements from A JSONB Object

I have a very large data table. The "properties" column contains jsonb objects. Very often I need to pull elements from this column. Specifically I pull unique values of all of the keys in ...
user avatar
  • 1
2 votes
2 answers
650 views

jsonb_array_elements() fails with "ERROR: cannot extract elements from an object"

Using: Postgres 14.2. Objective: To get a list of all distinct countries in my table. The column address is a JSONB column type and contains an array structured like: { "address":[ {...
user avatar
  • 123
0 votes
0 answers
30 views

How to include new key-value pair inside each object of JSON array in Postgres when retrieving records from a table?

I have below two tables in my database schema spec_attribute spec_attr_id spec_attr_name 1 Value1 2 Value2 3 Value3 product_variant_attribute product_id variant_attributes 1 [{"...
user avatar
  • 133
1 vote
1 answer
30 views

Simplify a jsonb request

I am a newbie in request involving jsonb. Can this one be improved? collections is a huge jsonb field and maybe one cross join is enough. SELECT actions FROM layouts CROSS JOIN ...
user avatar
  • 197
0 votes
1 answer
80 views

PostgreSQL get json key name after comparing values of keys

I have table which has a column of type json with name "demographic". I want to compare keys(predefined by me) by their values and the result of that should be the name of the key and not ...
user avatar
  • 1
0 votes
2 answers
82 views

Convert an optional JSONB field to a text array?

In my_table, the misc column is jsonb with default value {}. Some rows have misc.names as an array of text values; other rows do not have the misc.names key. I want to copy that data to a separate ...
user avatar
0 votes
1 answer
55 views

Flatten/ Concatenate Postgres JSONB array

I am trying to form a jsonb object (not array) from the table (t1) below id key value 1 hello world 1 dns 192.2.8.0 1 people 1000 I have tried SELECT jsonb_agg(('{"' || key || '" : &...
user avatar
1 vote
1 answer
48 views

design Postgres database schema for arbitrary JSON attribute queries

In my Postgres database, I have got lots of data (a million rows) and the schema looks like this: create table test_tb ( id int, tags jsonb ); A few queries to be performed on this dataset are: 1....
user avatar
  • 13
0 votes
1 answer
35 views

GROUP BY date column and then group by custom daytime

So I have to group a table according to the date and then the timeday when a product arrived, the timedays would be: morning = [5, 6, 7 , 8, 9] mid_morning = [10, 11] midday = [12, 13, 14] evening = [...
user avatar
  • 49
0 votes
1 answer
55 views

PostgreSQL jsonb as hash tables/dictionaries?

PostgreSQL does not seem to have a native data type for dictionaries. The new jsonb (and json) type seems to have the right interface: given a key, a jsonb object returns a value. I am just wondering ...
user avatar
  • 2,814