Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

How to construct a jsonb containing a number?

J
jules last edited by
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 the jsonb type and it only contains a numeric value 1.

But how do I create a jsonb with 1 in it directly without constructing and destructing an object?

Direct type casting does not seem to work:
```
# SELECT 1::jsonb;
ERROR:  cannot cast type integer to jsonb
LINE 1: SELECT 1::jsonb;
```
Reply Quote 0
1 Reply Last reply
A
amarantha last edited by
You need to cast from a string, not from a number: '1'::jsonb. This is because using '1' means that the result is actually a constant, whereas 1 is a runtime conversion.

to_jsonb(1) also works, but I think this uses a runtime conversion.
```
SELECT pg_typeof('1'::jsonb), jsonb_typeof('1'::jsonb);
```
pg_typeof jsonb_typeof

jsonb number

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=e60de76843bd07277dcbe651ab835411
Reply Quote 0
1 Reply Last reply

pg_typeof	jsonb_typeof
jsonb	number

2
Posts

0
Views

Suggested Topics

K

JSONB vs BRIN Index
SQL, Database Testing • postgresql postgresql 10 postgresql 11 • • kalena

2

0
Votes

2
Posts

0
Views

J

Assuming a mostly immutable set ~ 100 currencies overall (you haven't been clear on that), and your given requirements, consider the simple approach: 1 table with 1 row per user and 1 column per currency. Like: CREATE TABLE wallet ( user_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY , currency1 integer -- or numeric, depends on missing info , currency2 integer , ... , currency100 integer ); This has a massively smaller disk footprint than either of your two options so far. 4 bytes per currency in use (with integer), plus 16 bytes for the NULL bitmap. NULL storage is very cheap. See: Do nullable columns occupy additional space in PostgreSQL? Configuring PostgreSQL for read performance Data type integer or numeric? PostgreSQL: Which Datatype should be used for Currency? Your option 1 (jsonb) at least doubles the size per currency in use by storing a key name for every amount. Wins with only very few currencies per user, storage-wise. Sums, calculations, indexing are slower and more complicated. Data integrity is hard to enforce. Your option 2 occupies ~ 44 bytes per currency (separate row). Very clean data model, flexible for adding / removing currencies on the fly, but wastes a lot of space, which makes everything slow. A lot of reads for the whole wallet are as simple as: SELECT * FROM wallet WHERE user_id = 123; You only need an index on user_id, which is provided by the PK. Getting the nightly sum of total amount from all users for each currency is as simple and as fast as can be: SELECT sum(currency1), sum(currency2), ... FROM wallet; No index for that. If you have a couple of dozen currencies covering the lion's share of all entries, you could try a combined strategy: fixed columns for the regulars and a jsonb column for the rest. This combines minimum storage size with absolute flexibility - at the cost of more complicated queries and computations, as you have to combine both now. And much weaker means to enforce integrity. CREATE TABLE wallet ( user_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY , currency1 integer -- or numeric, depends on missing info , currency2 integer , ... , currency70 integer , chickenfeed jsonb ); I chose 70 currency columns to stay below the local optimum of 72 columns, before another 8 bytes are allocated for the NULL bitmap. A minor consideration. Chose a number that fits your data distribution. Maintain a table of all allowed currencies - you do not want to search millions of rows to get the complete list. And use minimum-length key names in the jsonb column, like '{"A1":123}' (2 bytes for the key) so not to waste GB of storage to repeating lengthy names over and over.
R

Pagination, check number of selected records
SQL, Database Testing • mysql innodb cache paging • • Rossere

2

0
Votes

2
Posts

0
Views

E

Why generate a temporary that drops on disconnect ? You would have to reload the table of ids, which requires extra code to manage the emptying and reloading. If you create a subquery that limits to 100 ids at a time, you could join to the cache_table: Just create an inline query for the first 1000 rows, then join SELECT ct.column_names FROM (SELECT id FROM comments WHERE user_id = 123 LIMIT 1000) c LEFT JOIN cache_table ct ON c.id = ct.id; If you have to paginate, adjust in the inline query to paginate on the IDs then join last SELECT ct.column_names FROM (SELECT id FROM comments WHERE user_id = 123 LIMIT 0,100) c LEFT JOIN cache_table ct ON c.id = ct.id; SIDE NOTE: I highly advise against creating temporary InnoDB tables. They are much harder to cleanup after a crash because of leftover entries in the ibdata1 file (home of the data dictionary). MyISAM temporary tables are easy to cleanup just by simply deleting the .MYD and .MYI files in the /tmp folder. UPDATE 2014-12-22 18:19 EST It sounds like you want Dynamic SQL based on the row count of tmp CREATE TEMPORARY TABLE IF NOT EXISTS tmp ENGINE=MyISAM SELECT id FROM comments WHERE user_id = 123; SELECT COUNT(1) INTO @reccount FROM tmp; SET @pg_origin = ???; SET @pg_offset = 100; SET @q1 = 'SELECT column_names FROM comments c JOIN tmp t ON c.id = t.id'; SET @q2 = CONCAT('SELECT column_names FROM comments c JOIN cache_table ct ON c.id = ct.id LIMIT ',@pg_offset,',',@pg_origin,';'); SET @sql = IF(@reccount < 1000,@q1,@q2); PREPARE s FROm @sql; EXECUTE s; DEALLOCATE PREPARE s; I still strongly recommend using MyISAM for the temp table since the row count is written in the .MYD header of the file (O(1) execution time to get the count) whereas an InnoDB temp table required physically counting the rows (O(n) execution time to get the count). As for the pagination, all you would need is to set @pg_origin to set where to begin retrieval. As for InnoDB temp tables, sometimes they do not properly drop on disconnect and leave pigeon holes in ibdata1. I wrote about this problem about 2.5 years ago mysql 5.5 corrupt - missing temporary table MySQL insists on trying to use InnoDB even when told not to, please help Table is dropped but ibd file still exists (Recent Post) UPDATE 2014-12-23 13:08 EST Your last comment Can you think about some other suggestions except UPDATE 2014-12-22 18:19 EST? Is it possible to somehow eliminate the use of a temporary table? If you look at the UPDATE 2014-12-22 18:19 EST, you'll notice that it only creates and loads the table once because I use CREATE TEMPORARY TABLE IF NOT EXISTS tmp instead of CREATE TEMPORARY TABLE tmp. If you know user_id 123 has new comments you would drop, create and load the temp table. Even if you don't want the temp table at all, you need to get the count anyway. So, go get count, move it around in a session variable from page to page. Then, apply the Dynamic SQL against that session count rather than having the table.
R

Select consecutive number of rows starting from some number
SQL, Database Testing • postgresql select • • rosemadder

2

0
Votes

2
Posts

0
Views

N

You can try something like SELECT field1, field2 FROM table1 ORDER BY ctid OFFSET x LIMIT 100 ; As I commented, without ordering you can never be sure that you get the same 100 rows. This way you will get the same rows in a certain order if they don't get updated or deleted.
N

Why does PostgreSQL keep resetting my date to the wrong "timezone", with "seconds", as I try to remove it?
SQL, Database Testing • postgresql date datetime timestamp postgresql 14 • • nishika

2

0
Votes

2
Posts

0
Views

S

'1792-03-29 00:00:00+01:12:12' is not what is in PG. That is the output format that PG dynamically produces in response to your 'timezone' configuration setting being applied to a timestamptz value. What is stored in the database is just some microseconds difference from a UTC-like reference. If your timezone setting doesn't change and the microsecond difference value stored doesn't change and PG internal table of timezone rules doesn't change, the dynamic output won't change either. You could always use text processing functions (either in PG or in PHP) to strip off the timezone if you don't want it. Or fix this "feature" in PHP strtotime function. Or just store things as text if you want the database to mindlessly regurgitate to you the same text you put in, without any comprehension of what the text might mean.
K

Postgrest api -- jsonb
Software Programming • postgresql jsonb • • Kadir

2

0
Votes

2
Posts

0
Views

R

To begin with a mandatory warning, what if you often have There's a need for such operations, and it's better to normalize the scheme. Next.You didn't specify the version. ♪ PostgreSQL version 12 and later request SQL/JSON Path:SELECT * FROM users WHERE objects @? '$[*] ? (@ == 2)' ;Because you have PostgRESTAnd he doesn't support syntaxis. SQL/JSON Path, you're gonna have to use language that's not compatible. previous versions PostgreSQL:SELECT * FROM users WHERE objects @> CAST('[2]' AS JSONB) ;According to the documentation, you'll have something like:/users?objects=cs. {2}
A

Row number with reset in PostgreSQL
SQL, Database Testing • postgresql postgresql 9.4 cte window func languagetions • • Amritpal

2

0
Votes

2
Posts

0
Views

S

Window functions and a single subquery: SELECT user_id, date, is_cleared , count(*) OVER (PARTITION BY user_id, grp ORDER BY date) AS custom_rank FROM ( SELECT user_id, date, is_cleared , count(is_cleared = 1 OR NULL) OVER (PARTITION BY user_id ORDER BY date DESC) AS grp FROM tbl ) t ORDER BY user_id, date; The special difficulty here: each partition ends with 1. In most similar cases, each partition would start with 1. The simple solution is to count occurrences of 1 in descending order. This way each partition includes the next 1 if it exists. Voilá. Assuming all involved columns to be NOT NULL and (user_id, date) unique. Basics: Select longest continuous sequence Asides: The first row of B gets custom_rank = 1. Not 2. The column is_cleared should really be boolean. A timestamp column shouldn't be called "date".
E

Receive JSONB facilities
Software Programming • sql postgresql jsonb • • elysha

2

0
Votes

2
Posts

0
Views

K

select id, metadata from tablename where exists ( select from jsonb_array_elements(metadata) as el where (el->'request'->>'status')::int in (1,2,3) ) For postgresql 12, it's still possible jsonpath:select id, metadata from tablename where metadata @? '$[*] ? (@.request.status == 1 || @.request.status == 2 || @.request.status == 3)'; The update can be recorded as follows:update tablename set metadata = ( select jsonb_agg( case when (el->'request'->>'status')::int in (1,2,3) then jsonb_set(el, array['request','status'], '7') else el end) from jsonb_array_elements(metadata) as el ) where ...
Y

ORDER BY that doesn't contain repeating values
SQL, Database Testing • postgresql select order by • • yajahira

2

0
Votes

2
Posts

0
Views

J

You cannot avoid in it in all cases (if more than 50% of the rows have the same value in the column.) One way to achieve a "shuffle" result, similar to what you want is by using window function: WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY colX) AS rn FROM tableX ) SELECT * FROM cte ORDER BY rn, colX ; The above will not avoid all cases though. If for example, the values in the column are 1,1,1,1,2,2,3, you'll get: 1,2,3,1,2,1,1 and not the (better): 1,2,1,3,1,2,1
E

Group by id and get avg time delta between grouped rows
SQL, Database Testing • postgresql aggregate postgresql 14 • • esther

2

0
Votes

2
Posts

0
Views

C

Something like: select id_user, (max(ordered_at)-min(ordered_at)) / (count(1)-1) from orders group by id_user having count(1) > 1; If you want to use lead (or lag) instead, you can determine the duration between two consecutive rows and then apply avg on that set: with t as ( select id_user, ordered_at , lead(ordered_at) over (partition by id_user order by ordered_at) as ld_ordered_at from orders ) select id_user, avg(ld_ordered_at - ordered_at) from t where ld_ordered_at is not null group by id_user; https://dbfiddle.uk/?rdbms=postgres_14&fiddle=caa07900f80e69bcfdfcccf4e49d779e The idea behind the first solution is that: (x2-x1)+(x3-x2)+...+(x_n+1-x_n) (-x1+(x2-x2)+(x3-x3)+...+(x_n-1-x_n-1)+x_n) (x_n-x1) and then divide with n-1 to get the average
Flatten/ Concatenate Postgres JSONB array
SQL, Database Testing • postgresql json jsonb • • Alberto

2

0
Votes

2
Posts

0
Views

J

turns out I was overcomplicating things and using the wrong function SELECT id, jsonb_object_agg(key, value) as kv_pair FROM t1 GROUP BY id
N

Processing of complex jsonb field
Software Programming • postgresql jsonb • • Nykeriab

2

0
Votes

2
Posts

0
Views

select id, (select sum((v->>'b')::int) from jsonb_each(data) as j(k,v)) from tablename
E

How to drop all indexes not used for primary keys or constraints
SQL, Database Testing • postgresql index index tuning ddl postgresql 14 • • emran

2

0
Votes

2
Posts

0
Views

To get a list: SELECT n.nspname AS schema, i.indrelid::regclass::text AS tbl, cl.relname AS idx, pg_get_indexdef(i.indexrelid) , 'DROP INDEX ' || i.indexrelid::regclass AS drop_cmd FROM pg_index i JOIN pg_class cl ON cl.oid = i.indexrelid JOIN pg_namespace n ON n.oid = cl.relnamespace LEFT JOIN pg_constraint co ON co.conindid = i.indexrelid WHERE n.nspname <> 'information_schema' -- ignore system schemas AND n.nspname NOT LIKE 'pg\_%' -- ignore system schemas AND co.conindid IS NULL -- no connected constraint AND NOT i.indisprimary -- not PK AND NOT i.indisunique -- not UNIQUE AND NOT i.indisexclusion -- not EXCLUDE AND NOT i.indisclustered -- not clustered AND NOT i.indisreplident -- not replicated ORDER BY 1, 2, 3; db<>fiddle https://dbfiddle.uk/?rdbms=postgres_14&fiddle=306f67c05ec8029fe5ec3210847607b5 This works for Postgres 14. Future versions may deviate. This lists every index that is not implemented by a constraint (PRIMARY, UNIQUE, EXCLUDE) - double-checked with a LEFT JOIN to pg_constraint to rule out any connection to any constraint. Plus, the index is not used to CLUSTER the table, nor for replication. More details in the manual about https://www.postgresql.org/docs/current/catalog-pg-index.html . Inspect the result. There may be any number of useful indexes among the rest. Like columns with outgoing FOREIGN KEY constraints, where an index is optional but often recommended ... Are you sure? (Are you sure you are sure?) You can drop one by one, or use this generated command to drop them all at once: SELECT 'DROP INDEX ' || string_agg(i.indexrelid::regclass::text, ', ' ORDER BY n.nspname, i.indrelid::regclass::text, cl.relname) AS drop_cmd FROM pg_index i JOIN pg_class cl ON cl.oid = i.indexrelid JOIN pg_namespace n ON n.oid = cl.relnamespace LEFT JOIN pg_constraint co ON co.conindid = i.indexrelid WHERE n.nspname <> 'information_schema' AND n.nspname NOT LIKE 'pg\_%' AND co.conindid IS NULL -- no connected constraint AND NOT i.indisprimary AND NOT i.indisunique AND NOT i.indisexclusion AND NOT i.indisclustered AND NOT i.indisreplident; If there are concurrent connections to the database, don't use this monster-command, as it will block the whole DB, possibly for an extended period of time. And it may easily deadlock.
C

Numbering rows consecutively for a number of tables
SQL, Database Testing • postgresql update sc languageripting window func languagetions • • Cleofas

2

0
Votes

2
Posts

0
Views

If I understand correctly, you want to restart the numbering with 0 for every table. Use the window function row_number() in an UPDATE: UPDATE tbl t SET xid = n.xid FROM (SELECT ctid, row_number() OVER (ORDER BY aid, bid, cid) - 1 AS xid FROM tbl) n WHERE t.ctid = n.ctid; Using ctid as poor man's surrogate for a primary key, since you neglected to disclose your table definition. SQL Fiddle. db<>fiddle here
P

trigram matching on a jsonb array
SQL, Database Testing • postgresql string searc languagehing • • Pearlaqua

2

0
Votes

2
Posts

0
Views

A

You can't index a rows-returning expression, so it would probably be best to normalize your data with one nickname per row, rather all stuffed into an array. Then you could index the "nickname" table and join it to the "persons" table. If you don't want to do that, you could transform the name field into a string, and index that. This could lead to unwanted results if the query could span over the delimiter and still meet the cutoff (not very likely, unless the query also contains punctuation), so the queries are not identical, but might be good enough. create or replace function arr_val_agg(jsonb) returns text as $$ select string_agg(bar.value,', ') from jsonb_array_elements($1) join lateral jsonb_each_text(value) bar on true; $$ language SQL immutable; create index on persons using gin (arr_val_agg(name) gin_trgm_ops ); select * from persons where arr_val_agg(name) %> 'jhon'; As the lengths of arr_val_agg get longer, the more false positives you are likely to get from the trigram index which need to be filtered out by a recheck, so performance might suffer.
S

Finding the highest number of intersections in between intervals?
SQL, Database Testing • sqlite • • simrah

2

0
Votes

2
Posts

0
Views

D

-- first put all the timestamps, counting +1 for every "entrance" (start) -- and -1 for every "exit" (end) WITH c AS ( SELECT start AS dt, +1 AS counter FROM periods UNION ALL SELECT end AS dt, -1 AS counter FROM periods ), -- then calculate the running totals, going through the counters -- in order of the timestamps cc AS ( SELECT SUM(SUM(counter)) OVER (ORDER BY dt) AS running_total FROM cc GROUP BY dt ) -- then find the maximum of the running totals SELECT MAX(running_total) FROM cc;

How to construct a jsonb containing a number?

Suggested Topics

JSONB vs BRIN Index SQL, Database Testing • postgresql postgresql 10 postgresql 11 • • kalena

Pagination, check number of selected records SQL, Database Testing • mysql innodb cache paging • • Rossere

Select consecutive number of rows starting from some number SQL, Database Testing • postgresql select • • rosemadder

Why does PostgreSQL keep resetting my date to the wrong "timezone", with "seconds", as I try to remove it? SQL, Database Testing • postgresql date datetime timestamp postgresql 14 • • nishika

Postgrest api -- jsonb Software Programming • postgresql jsonb • • Kadir

Row number with reset in PostgreSQL SQL, Database Testing • postgresql postgresql 9.4 cte window func languagetions • • Amritpal

Receive JSONB facilities Software Programming • sql postgresql jsonb • • elysha

ORDER BY that doesn't contain repeating values SQL, Database Testing • postgresql select order by • • yajahira

Group by id and get avg time delta between grouped rows SQL, Database Testing • postgresql aggregate postgresql 14 • • esther

Flatten/ Concatenate Postgres JSONB array SQL, Database Testing • postgresql json jsonb • • Alberto

Processing of complex jsonb field Software Programming • postgresql jsonb • • Nykeriab

How to drop all indexes not used for primary keys or constraints SQL, Database Testing • postgresql index index tuning ddl postgresql 14 • • emran

Numbering rows consecutively for a number of tables SQL, Database Testing • postgresql update sc languageripting window func languagetions • • Cleofas

trigram matching on a jsonb array SQL, Database Testing • postgresql string searc languagehing • • Pearlaqua

Finding the highest number of intersections in between intervals? SQL, Database Testing • sqlite • • simrah

JSONB vs BRIN Index
SQL, Database Testing • postgresql postgresql 10 postgresql 11 • • kalena

Pagination, check number of selected records
SQL, Database Testing • mysql innodb cache paging • • Rossere

Select consecutive number of rows starting from some number
SQL, Database Testing • postgresql select • • rosemadder

Why does PostgreSQL keep resetting my date to the wrong "timezone", with "seconds", as I try to remove it?
SQL, Database Testing • postgresql date datetime timestamp postgresql 14 • • nishika

Postgrest api -- jsonb
Software Programming • postgresql jsonb • • Kadir

Row number with reset in PostgreSQL
SQL, Database Testing • postgresql postgresql 9.4 cte window func languagetions • • Amritpal

Receive JSONB facilities
Software Programming • sql postgresql jsonb • • elysha

ORDER BY that doesn't contain repeating values
SQL, Database Testing • postgresql select order by • • yajahira

Group by id and get avg time delta between grouped rows
SQL, Database Testing • postgresql aggregate postgresql 14 • • esther

Flatten/ Concatenate Postgres JSONB array
SQL, Database Testing • postgresql json jsonb • • Alberto

Processing of complex jsonb field
Software Programming • postgresql jsonb • • Nykeriab

How to drop all indexes not used for primary keys or constraints
SQL, Database Testing • postgresql index index tuning ddl postgresql 14 • • emran

Numbering rows consecutively for a number of tables
SQL, Database Testing • postgresql update sc languageripting window func languagetions • • Cleofas

trigram matching on a jsonb array
SQL, Database Testing • postgresql string searc languagehing • • Pearlaqua

Finding the highest number of intersections in between intervals?
SQL, Database Testing • sqlite • • simrah