In short – use JSONB instead of JSON or cast JSON to JSONB. You cannot compare json values. You can compare text values instead: SELECT * FROM movie_test WHERE tags::text=”[“dramatic”,”women”,”political”]” Note however that values of type JSON are stored as text in a format in which they are given. Thus the result of comparison depends … Read more
Congratulations! By exposing a database with a weak superuser password to the internet you invited somebody to break in and use your CPU for their own purposes, probably mining crypto-currencies. Take the machine from the internet, wipe it clean and re-install the operating system.
You are not actually aggregating rows, so the new aggregate FILTER clause is not the right tool. A window function is more like it, a problem remains, however: the frame definition of a window cannot depend on values of the current row. It can only count a given number of rows preceding or following with … Read more
There are two operations to get value from JSON. The first one -> will return JSON. The second one ->> will return text. Details: JSON Functions and Operators Try SELECT (json_data->’position’->>’lat’)::float + 1.0 AS lat FROM updates LIMIT 5
Answer DO expects a string literal with plpgsql code. Symbols are not substituted inside strings in psql. You could concatenate the whole string into a psql variable and then execute it. How to concatenate psql variables? Pretty multi-line format is not possible, because (per documentation): But in any case, the arguments of a meta-command cannot … Read more
This particular case The function below dynamically creates a view based on a table: create or replace function create_totals_view(table_name text) returns void language plpgsql as $$ declare s text; begin execute format ($fmt$ select string_agg(format(‘star_pu->>”%s” “%s”‘, key, key), ‘,’) from ( select distinct key from %s, json_each(star_pu) order by 1 ) s; $fmt$, ‘%s’, ‘%s’, … Read more
If you’re able to upgrade to Postgresql 9.5, the jsonb_set command is available, as others have mentioned. In each of the following SQL statements, I’ve omitted the where clause for brevity; obviously, you’d want to add that back. Update name: UPDATE test SET data = jsonb_set(data, ‘{name}’, ‘”my-other-name”‘); Replace the tags (as oppose to adding … Read more