1
\u0395 is just 6 characters to MariaDB. Searching for the 5-character "word" u0395 would succeed.
You need to change the JSON strings to have characters, not unicode codepoint encoding.
In PHP, I would consider write a 1-time script to bring in each line, decode it, and write it back out.
Meanwhile, the writing should be changed to
json_encode($...
1
Set returning functions should be called in the FROM.
SELECT mt.measurement_id,
p.port ->> 'port_name' as "port_name",
p.port ->> 'port_description' as "port_description",
p.port ->> 'port_status_int' as "port_status_int",
p.port ->> 'port_status' as "port_status",
...
1
The immediate reason for the error message is that the data type json has no equality operator. See:
How to query a json column for empty objects?
How to remove known elements from a JSON[] array in PostgreSQL?
You have:
...
group by
A.name,
B.products -- type json!?
You can do that, using jsonb instead of json, where an equality operator is ...
1
You could keep your current UPDATE statement, but add WHERE data ? 'my-value' to it to avoid updating rows which have nothing you want to update.
When v13 is released, you could use the new jsonb_set_lax function:
UPDATE cars SET data=jsonb_set_lax(
data,
'{new-attr}',
data->'my-value',
true,
'return_target');
But it seems like more ...
1
The concatenation operator || will do that:
update the_table
set data = data || jsonb_build_object('new-attr', data -> 'my-value')
where data ? 'my-value'; --<< only update rows that contain the key
The ? operator tests if the key is present in the JSON value.
If your column is defined as json (rather than jsonb which it should be), you probably ...
Only top voted, non community-wiki answers of a minimum length are eligible
