\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
Set returning functions should be called in the FROM.
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",
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?
B.products -- type json!?
You can do that, using jsonb instead of json, where an equality operator is ...
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(
But it seems like more ...
The concatenation operator || will do that:
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 ...