Questions tagged [jsonb]
The jsonb tag has no usage guidance.
45
questions
0
votes
0
answers
41
views
Recursive Breadth Traversal Query (with max depth), Returning Nested JSON, Filtering the Initial Seed
Since there wasn't much activity here, I've also asked this question on regular StackOverflow. If you happen to successfully answer here, do paste it there so you get more points.
In my DB, I have ...
- 121
0
votes
1
answer
50
views
Issues using a psql -v variable with jsonb_set
Pretty obscure situation and a couple hours of trying different things with no luck.
I have a bash script which restores a production database backup locally. Part of the script runs some SQL to set ...
- 103
2
votes
1
answer
63
views
json_array_elements returns empty dataset in postgres
I was trying to read elements from Json array inside another array that is stored on Postgres database, but my result set was always empty.
My table looks like below:
Sample Json from table:
{"...
- 23
0
votes
0
answers
43
views
Find maximum of 'jsonb_path_query_array' output
I'm using postgres with a jsonb field that looks something like this:
{[[1.2, 'blah'], [3.4, 'blahhh']}
I'm extracting the numbers by using:
jsonb_path_query_array(json_row, '$[*][0]')
Which gives ...
- 101
0
votes
0
answers
104
views
Performance tuning for jsonb column containing nested json arrays
I have a jsonb column in a table with the following model:
{
"framework": {
"name": "framework name",
"sections": [
{
"controls&...
0
votes
0
answers
228
views
MySQL TEXT vs JSON column
We are using MySQL 5.7 and has recently introduced a new DB schema which basically stores key string and value json.
Currently it is designed as
CREATE TABLE foo (
key VARCHAR(124) NOT NULL,
...
- 101
0
votes
1
answer
179
views
how to contraint that jsonb column must be a json/object?
I have a column that have json datatype
how to make a constraint or validation in my postgresql to validate that this jsonb column value must be a hash/object/json
because apparently after some ...
- 103
2
votes
2
answers
483
views
PostgreSQL: Insert dynamic columns and values from jsonb argument
This question relates to Update dynamic column names using keys from jsonb argument and my code is based on the answer provided there by https://dba.stackexchange.com/users/3684/erwin-brandstetter
I'm ...
0
votes
1
answer
77
views
Is it possible an already big table with jsonb doubled in size by just adding an empty jsonb property on each record? with postgres 12.7
We ran a migration on a table which contain a big jsonb.
"Just" one new attribute was added inside the jsonb: "someAttribute": null was added on each row.
After this migration, the ...
- 215
0
votes
1
answer
279
views
Resultset from jsonb array
I have a function that build a JSONB array of object.
CREATE OR REPLACE FUNCTION my_func()
RETURNS TABLE(field1 INT, field2 TEXT)
In DECLARE part:
DECLARE
r RECORD;
d JSONB;
d_list ...
- 67
1
vote
1
answer
206
views
JSON colunm vs Reference key approach - Postgres
Which is a good database schema design by considering the performance and long-term large records?
Database: Postgres
I have two ways to design the table structure:
Use a JSON column
Go with a ...
- 113
0
votes
0
answers
30
views
Joining Two Tables via JSONB column (date)
Given the following data:
create table datas (id int, data jsonb);
insert into categories (id, budget)
values
(1, '{"2022-07-01": {"budget_amount": value1, "some": &...
- 43
4
votes
2
answers
438
views
Pair top-level key with nested value from jsonb document
Given the following data:
create table datas (id int, data jsonb);
insert into datas (id, data)
values
(1, '{"key1": {"foo": "bar1","some":"thing1&...
- 43
4
votes
1
answer
445
views
Using collations with JSONB
I have a postgres database and a case insensitive collation.
The collation was created by
CREATE COLLATION IF NOT EXISTS case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = ...
- 141
0
votes
1
answer
45
views
How to append 2 default values to a JSONB column in Postgres SQL 14?
Previously I had 4 default values for a jsonb column in my table cities.
create_table "cities", force: :cascade do |t|
t.jsonb "config", default: {"A"=&...
0
votes
0
answers
103
views
Get index of JSON data in PostgreSQL
I have the next JSON structure in my PosgreSQL:
[
{
"name": "Ryzen 9",
"path": "fitnessappmaven\\src\\main\\java\\com\\klaro\\fitnessappmaven\\tempWorkoutIcons\\...
0
votes
1
answer
215
views
[Resolved]: How to define the query for group by when using jsonb_query_path functions in the SELECT
I am trying to define the following GROUP BY query and need some help. I am using PostgreSQL DB table with a jsonb column
select
count(*),
policynumber
from
(
select
'My ...
- 103
0
votes
0
answers
316
views
How to get parent node based on jsonpath in PostgreSQL
I am trying to figure out a way to extract information from huge json value in a PostgreSQL jsonb column and need some help with below situation. Not sure, if it can be achieved. The json is about ...
- 103
1
vote
1
answer
237
views
Join on table with common part in jsonb in nested array
I am having hard time trying to figure out this join.
I have two related tables:
Reservations
id | data
----+------------
1 | something
CalendarEvents
id | ...
- 13
0
votes
1
answer
87
views
Postgres 11 join tables using jsonb values
I have two tables:
create table device_group_v2
(
id uuid not null primary key,
name varchar(255),
filter jsonb
);
with one entry:
+--------------------------------------+--...
- 127
0
votes
1
answer
590
views
postgres reverse jsonb array
Postgres 13
I have a table with jsonb array and I need to revert the array. Any idea on how I can do that?
CREATE OR REPLACE FUNCTION array_reverse(anyarray) RETURNS anyarray AS $$
SELECT ARRAY(
...
- 101
0
votes
1
answer
2k
views
How to select all nested values at a particular level in a json structure in a JSONB type column in postgresql?
In a postgresql database, I have a table with 2 columns:
Column1 is of type VARCHAR and contains multiple rows of ids in this format: ID1123312
Column2 is of type JSONB and contains multiple rows of ...
- 105
0
votes
0
answers
222
views
What should I choose between jsonb array and jsonb map for faster querying?
I have a jsonb column which will consist of some keys and the rows will be searchable using those keys. Considering that I will be applying GIN index on the jsonb column what should be the ideal ...
0
votes
1
answer
419
views
How do I query inside a JSONB column when all values have the same json-like structure?
In a postgresql database, I have a table with 2 columns:
Column1 is of type VARCHAR and contains multiple rows of ids in this format: ID1123312
Column2 is of type JSONB and contains multiple rows of ...
- 105
0
votes
0
answers
173
views
select by aggregation of fields in json column and sum
Using postgresql 11.
Given the following dataset:
foo | bar
-------------------+--------------------------------------------------
"a" | {'a_count': 1, '...
- 101
1
vote
1
answer
5k
views
Add key and value to elements of a jsonb array where the key does not exist yet
I have a table tbl with the following structure:
CREATE TABLE tbl (
org text
, data jsonb
);
The data in the jsonb field is an array structured in the following way:
INSERT INTO tbl VALUES
('...
- 23
0
votes
1
answer
1k
views
Changing a single element of an json array in PostgreSQL
First of all I'd like to say that I don't have much experience but I started learning dbs after having a couple of subjects related to dbs at my university (so please keep that in mind while you're ...
- 3
0
votes
1
answer
246
views
parse JSON keys preserving ordinality
I had heard that in postgresql, the json type preserves declared ordering unlike it's fully-parsed counterpart jsonb.
I want to get the ordinal position of each key in a document as supplied by the ...
- 4,531
3
votes
1
answer
953
views
Update all instances of key inside nested JSON array
I'm trying to update a complex JSON document stored in a table in CockroachDB.
The CockroachDB query language is mostly Postgres compatible except for the lack of procedural code, so a Postgres answer ...
- 31
0
votes
1
answer
216
views
How to query nested unamed jsonb objects and order?
have an audits column (jsonb) seems like:
{{"status": "pending", "updated_at": "2022-06-20T15:58:54-03:00"},{"status": "active", "...
0
votes
0
answers
53
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,343
0
votes
2
answers
138
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,343
1
vote
1
answer
282
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": [
{
&...
2
votes
1
answer
1k
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 ...
- 121
0
votes
0
answers
405
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
3k
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 ...
- 3,315
6
votes
2
answers
13k
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":[
{...
- 163
1
vote
1
answer
93
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 ...
- 215
0
votes
1
answer
1k
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 ...
- 101
0
votes
2
answers
5k
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 ...
- 841
0
votes
1
answer
714
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
188
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
504
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 = [...
- 149
0
votes
1
answer
631
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 ...
- 3,315
2
votes
1
answer
1k
views
Remove the deeper jsonb element in Postgres [duplicate]
[
{
"type":"test.added",
"values":[
{
"field":"field1",
"value":"fieldValue1"
...
