Questions tagged [jsonb]
The jsonb tag has no usage guidance.
17
questions
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->'...
- 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 , ...
- 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@...
- 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": [
{
&...
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 ...
- 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": ...
- 272
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 ...
- 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 ...
- 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":[
{...
- 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
[{"...
- 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 ...
- 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 ...
- 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 ...
- 751
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 || '" : &...
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....
- 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 = [...
- 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 ...
- 2,814