0

I need to update my JSON object to see if it matches the condition. For eg: I need to replace "foo" to "loo":

I tried using

select (
case when (
json_extract_path_text( '{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6') = "foo") then "loo"
else "oo" end)

It gives me an ERROR: column "foo" does not exist on ="foo".

How can I replace text in my JSON object using Postgres?

CoderBeginner
  • 717
  • 1
  • 12
  • 39
  • `"foo"` references the db object `foo`, `'foo'` is the string with a value of `foo` I believe. Just to clarify why it's saying column `"foo"` doesn't exist. – CollinD Oct 08 '22 at 05:54
  • @CollinD, Yes "f6":"foo" I am referring to, I need to update "f6" value from "foo" to "loo". ="foo" is the error line. How should I refer to "foo". Any idea? – CoderBeginner Oct 08 '22 at 06:13
  • 1
    Does it answer your question https://stackoverflow.com/questions/18209625/how-do-i-modify-fields-inside-the-new-postgresql-json-datatype ? – Serg Oct 08 '22 at 06:52
  • 1
    could you use single quotes instead of double. double quotes are used for sql identifiers (tables, columns, schemas, etc.), while single quotes represent text content. – Haleemur Ali Oct 08 '22 at 16:30
  • How can I update the object value: My Object structure is : [{"id":1,name:"a", "type":{"property:[{"id":2,"value":"ABC"}]. I tried using update table set data = JSONB_SET(data::jsonb, '{type}'::text[], data ->'type'||'{"id":4}', true) But it gives me operator -> unknown error. How can I update the "id" to 4? – CoderBeginner Oct 09 '22 at 07:46

0 Answers0