The documentation says: The || operator concatenates the elements at the top level of each of its operands. … For example, if both operands are objects with a common key field name, the value of the field in the result will just be the value from the right hand operand. So using your example data: … Read more
To add the value use the JSON array append opperator (||) UPDATE jsontesting SET jsondata = jsondata || ‘[“newString”]’::jsonb WHERE id = 7; Removing the value looks like this UPDATE jsontesting SET jsondata = jsondata – ‘newString’ WHERE id = 7; Concatenating to a nested field looks like this UPDATE jsontesting SET jsondata = jsonb_set( … Read more
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
You should become familiar with JSON Functions and Operators. — #1 select * from example where content->’Item’->>’Name’ ilike ‘%dog%’ and content->’Item’->>’Spec’ ilike ‘%red%’ — #2 select * from example where content->’Item’->>’Name’ ilike ‘%dog%’ or content->’Item’->>’Spec’ ilike ‘%red%’ — #3 select distinct on(no) t.* from example t, lateral jsonb_each_text(content->’Item’) where value ilike ‘%dog%’; — and select … Read more
There is no single operation, which can help you, but you have a few options: 1. If you have a small (and fixed) number of ids to query, you can use multiple containment operators @> combined with or; f.ex.: where data @> ‘[{“id”: “1884595530”}]’ or data @> ‘[{“id”: “791712670”}]’ A simple gin index can help … Read more
For the number of bytes used to store: select id, pg_column_size(datab) from data.items; For the number of elements on the jsonb object: select id, jsonb_array_length(datab) from data.items;
In Postgres 11 or earlier it is possible to recursively walk through an unknown json structure, but it would be rather complex and costly. I would propose the brute force method which should work well: select * from reports where params::text like ‘%authVar%’; — or — where params::text like ‘%”authVar”%’; — if you are looking … Read more
In UPDATE use delete (-) and concatenate (||) operators, e.g.: create table example(id int primary key, js jsonb); insert into example values (1, ‘{“nme”: “test”}’), (2, ‘{“nme”: “second test”}’); update example set js = js – ‘nme’ || jsonb_build_object(‘name’, js->’nme’) where js ? ‘nme’ returning *; id | js —-+————————- 1 | {“name”: “test”} 2 … Read more
ALTER TABLE table_with_json ALTER COLUMN my_json SET DATA TYPE jsonb USING my_json::jsonb;
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