Linked Questions

1 vote
0 answers
734 views

Convert Integer Array to JSON Array

I'm not sure how I should go about converting int[] to json[], here's what I've tried so far: db=# alter table product alter column images type json[] using images::json[]; ERROR: cannot cast type ...
Shayan's user avatar
  • 111
1 vote
1 answer
6k views

select json b field of array postgresql of an array field in json

*Postgresql version: PostgreSQL 13.5 alter table accounts add tags jsonb default '[]'::jsonb not null; select DISTINCT AC.tags from accounts as AC where account_type = 'TEAM'; this query ...
tero17's user avatar
  • 119
0 votes
2 answers
8k 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
19 votes
1 answer
9k views

What does BEGIN ATOMIC mean in a PostgreSQL SQL function / procedure?

On PG 14's documentation, in the CREATE FUNCTION section, the manual says that the body of a LANGUAGE SQL function can either be a single statement: RETURN expression or a block: BEGIN ATOMIC ...
ARX's user avatar
  • 1,439
1 vote
1 answer
14k views

PostgreSQL Convert JSONB array to string

I am trying to flatten a nested JSONB object into a string so that I can run regex on it. To retrieve the relevant text fields, I'm using SELECT jsonb_path_query_array(posts.content, 'strict $.**.text'...
Sentient's user avatar
  • 111
0 votes
1 answer
7k views

How to convert strings extracted from JSON to a numeric type for aggregates?

I am struggling to extract data from this JSON object in PostgreSQL: "temperature": { "boiler_temp": { "values": [ "1", "2", "3&...
Liu Jin Long's user avatar
1 vote
1 answer
2k views

How to sum the values ​of a json column filtered with regex?

How to sum the values ​​of a json column filtered with regex? Columns: A.name: Varchar with name product B.products: JSON with time active By product Query: select A.name as product, sum(((...
Luis Henrique's user avatar
1 vote
1 answer
20k views

How to loop through JSON array of JSON objects to see if it contains a value that I am looking for in postgres?

Here is an example of the json object rawJSON = [ {"a":0, "b":7}, {"a":1, "b":8}, {"a":2, "b":9} ] And I have a table that essentially looks like this. demo Table id | ...(other columns)...
user3441114's user avatar
8 votes
1 answer
3k views

When to mark functions as PARALLEL RESTRICTED vs PARALLEL SAFE?

The manual for Postgres 12 says: The following operations are always parallel restricted. Scans of common table expressions (CTEs). Scans of temporary tables. ... Further down on ...
Erwin Brandstetter's user avatar
3 votes
3 answers
7k views

How do I select by a JSONB array containing at least one element within a range?

I need to model products with properties. Properties need to be dynamic, in the sense that application users can add / delete properties to products. For this reason, I am using a JSONB column ...
yakutori's user avatar
10 votes
1 answer
13k views

Extract JSON array of numbers from JSON array of objects

I have a table containing a json array of objects similar to this: id | record ____________________ name1 | [{"a":0, "b":x}, {"a":1, "b":y}, {"a":2, "b":z}, ...] I would like to get a table ...
Vakarelov's user avatar
  • 103
10 votes
2 answers
14k views

How to combine ORDER BY and LIMIT with an aggregate function?

Here is a fiddle for my question. I have a simple table layout: class person: belongs to a class I want to select all classes, and for each class, I want the first two person identifiers of the ...
Jarius Hebzo's user avatar
12 votes
3 answers
32k views

How to unnest and GROUP BY elements of a JSON array?

Given the band table, with a json column holding an array : id | people ---+------------- 1 | ['John', 'Thomas'] 2 | ['John', 'James'] 3 | ['James', 'George'] How to list the number of bands each ...
Bax's user avatar
  • 303
31 votes
4 answers
47k views

Unquoting JSON strings; print JSON strings without quotes

SELECT json_array_elements('["one", "two"]'::json) gives result | json_array_elements | | :------------------ | | "one" | | "two" | I would like to ...
Maxim Yefremov's user avatar
13 votes
4 answers
6k views

Is it REALLY possible that the order will not be guaranteed for this particular redundant derived table?

I stumbled upon this question on a Twitter conversation with Lukas Eder. Although the correct behavior would be to apply the ORDER BY clause on the outermost query, because, here, we are not using ...
Vlad Mihalcea's user avatar

15 30 50 per page