Questions tagged [jsonb]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
-1 votes
1 answer
22 views

POSTGRES : Data import

DB Version : 13.x Source table schema\ CREATE TABLE mig_bus_data mig_id SERIAL, mig_data JSONB Target table schema\ CREATE TABLE BUSINESS id UNIQUE SERIAL, data1 <TYPEx&...
CSP's user avatar
  • 11
1 vote
1 answer
36 views

will null-ing JSONB column in postgres table before performing ALTER TABLE DROP of that column help?

I changed table structure and I am about to remove old JSONB column from about 2.5M rows big table. The JSONB makes this table really big - pg_total_relation_size: 408GB Performing ALTER TABLE ... ...
andilabs's user avatar
  • 637
0 votes
1 answer
217 views

Computing hashes in PostgreSQL for JSONB columns

From PostgreSQL docs: jsonb does not preserve white space, does not preserve the order of object keys, and does not keep duplicate object keys. If duplicate keys are specified in the input, only the ...
VIAGC's user avatar
  • 103
0 votes
1 answer
145 views

EAV or JSONB column for dynamic data that needs good indexing

I have some data in a Postgres database that does not fit into a static schema. There is a table "content" and each piece of content has a user-defined type. Each type can have different ...
Fabian's user avatar
  • 103
1 vote
0 answers
106 views

like_regex vs jsonb_build_object performance

I'm working with Postges 14.8 and using JSONB to store data. The queries that I'm working with perform filtering and JOIN'ing based on stuff in this JSONB column named data. We need to perform JOIN's ...
MRzeczkowski's user avatar
0 votes
1 answer
87 views

How do you sum **sub-elements** of a JSON field?

In PostgreSQL, how do you sum sub-elements of a JSON field? Given: Customer <--- Order where Order has a JSON field quantities_per_product that contains the qty and shipped qty for each product of ...
Michael's user avatar
  • 153
1 vote
0 answers
35 views

How to Preserve Past Order Details Amidst Customer Information Changes?

I'm facing a scenario in my e-commerce setup where after an order is marked as Done, any subsequent changes made by the customer to their information (like shipping address) are reflected incorrectly ...
Ashwin Jacob's user avatar
0 votes
1 answer
71 views

User-defined aggregate function to sum jsonb values

I have agg function that sums decimals. Business logic omitted for brevity CREATE OR REPLACE FUNCTION calculate_snapshot_internal_decimal(_this_value DECIMAL, ...
Capacytron's user avatar
0 votes
0 answers
92 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 ...
psygo's user avatar
  • 121
0 votes
1 answer
191 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 ...
Luke Belbina's user avatar
2 votes
1 answer
247 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: {"...
Karthika's user avatar
0 votes
0 answers
142 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 ...
Nathaniel Bubis's user avatar
0 votes
0 answers
310 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&...
Hardik Parikh's user avatar
0 votes
0 answers
962 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, ...
rajeshnair's user avatar
0 votes
1 answer
471 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 ...
buncis's user avatar
  • 103
2 votes
2 answers
1k 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 ...
Russell Turner's user avatar
0 votes
1 answer
91 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 ...
Slim's user avatar
  • 237
0 votes
1 answer
504 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 ...
ceadreak's user avatar
2 votes
1 answer
376 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 ...
Karthik's user avatar
  • 123
0 votes
0 answers
56 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": &...
RMcLellan's user avatar
4 votes
2 answers
794 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&...
RMcLellan's user avatar
4 votes
1 answer
680 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 = ...
Pux's user avatar
  • 141
0 votes
1 answer
88 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"=&...
Priyanshu0007's user avatar
0 votes
0 answers
113 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\\...
Robin Gergelyfi's user avatar
0 votes
1 answer
328 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 ...
adbdkb's user avatar
  • 103
1 vote
1 answer
345 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 | ...
Niph's user avatar
  • 13
0 votes
1 answer
175 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: +--------------------------------------+--...
Alex Tbk's user avatar
  • 127
0 votes
1 answer
934 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( ...
Daniel Kniaz's user avatar
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 ...
JustNatural's user avatar
0 votes
0 answers
341 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 ...
user2375245's user avatar
0 votes
1 answer
570 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 ...
JustNatural's user avatar
0 votes
0 answers
188 views

select by aggregation of fields in json column and sum

Using postgresql 11. Given the following dataset: foo | bar -------------------+-------------------------------------------------- "a" | {'a_count': 1, '...
ChuckE's user avatar
  • 101
1 vote
1 answer
7k 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 ('...
sharknado's user avatar
0 votes
1 answer
2k 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 ...
Leahim's user avatar
  • 3
0 votes
1 answer
395 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 ...
Peter Vandivier's user avatar
3 votes
1 answer
1k 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 ...
JRL's user avatar
  • 31
0 votes
1 answer
240 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", "...
Christian Adão Ferreira's user avatar
0 votes
0 answers
70 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->'...
Kokizzu's user avatar
  • 1,363
0 votes
2 answers
263 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 , ...
Kokizzu's user avatar
  • 1,363
1 vote
1 answer
346 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": [ { &...
rocketboy2000's user avatar
3 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 ...
tzi0's user avatar
  • 131
0 votes
0 answers
470 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": ...
Element Zero's user avatar
2 votes
1 answer
4k 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 ...
tinlyx's user avatar
  • 3,540
8 votes
2 answers
19k 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":[ {...
bob_cobb's user avatar
  • 183
1 vote
1 answer
115 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 ...
Slim's user avatar
  • 237
0 votes
1 answer
2k 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 ...
eko's user avatar
  • 101
0 votes
2 answers
7k 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 ...
Nathan Long's user avatar
0 votes
1 answer
875 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 || '" : &...
thesunnyscientist's user avatar
1 vote
1 answer
203 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....
Maniish's user avatar
  • 13
0 votes
1 answer
545 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 = [...
IBot's user avatar
  • 149