0

I have a table with a json field. I plan on storing a json array of tags in that field e.g. ["beach","car","phone"]. How do I perform actions like, add a tag to the array, delete a tag from the array or select where is contained in the json array field.

I am currently running postgresql9.3. In short I want to implement an 'insert/update' function on that field. Is it possible to achieve this in SQL only? Thanks in advance

flexxxit
  • 2,440
  • 5
  • 42
  • 69
  • In 9.3, it'll be clumsy and difficult. In 9.4, slightly less so. Neither will be _efficient_. – Craig Ringer Jun 16 '14 at 12:04
  • @CraigRinger Thanks for the input. Even if clumsy I wont mind seeing a possible solution? – flexxxit Jun 17 '14 at 09:24
  • Hm, the lack of an equality operator for `json` actually makes this really hard, much harder than I thought. An ugly but usable approach like `SELECT array_to_json(array_agg(x)) from (SELECT x FROM json_array_elements('["beach","car","phone"]'::json) x UNION ALL SELECT '"x"') y;` works for adding an elem, but `union` instead of `union all` to merge doesn't work because of the lack of an equals op. – Craig Ringer Jun 17 '14 at 13:49
  • @CraigRinger Hm,that will mean duplicate tags. Can't have that. A tough one there. Can I use PLV8 with Postgresql9.3 instead; will that be easier and more efficient? – flexxxit Jun 18 '14 at 10:52
  • Certainly easier, yes. – Craig Ringer Jun 18 '14 at 11:07

0 Answers0