CODEWITHSUNDEEP
sqlpostgresqlsql-update
t9217
t9217

Reputation: 117

Remove spaces from UPDATE query

I'm updating a table in postgres database, but i need to remove some spaces between the ":" string value.

I nedd a string like this: 

{"value1":"answer1", "value2":"answer2", "nickname":"name1", "color":"red" }

But the following code is giving me with some spaces like this:

 {"value1":"answer1", "value2":"answer2", "nickname" : "name1", "color" : "red" }

Here is my update code:

UPDATE t 
    SET    json_col = (Json_build_object ('nickname',nick,'color',color) :: text,'{', '' ) ) :: json;

Upvotes: 0

Views: 355

Answers (1)

GMB
GMB

Reputation: 222432

These whitepaces should not be a problem to you. In the IEFT JSON specification, it is clearly stated that spaces are allowed around structural characters (the : being one of them) :

Insignificant whitespace is allowed before or after any of the six structural characters.

  ws = *(
         %x20 /              ; Space
          %x09 /              ; Horizontal tab
          %x0A /              ; Line feed or New line
          %x0D )              ; Carriage return

In this regard the JSON you exhibited fully conforms to the spec.

There are functions in Postgres to pretty print JSON but unfortunately not to tidy it.

It is always possible to manipulate a JSON string with a regular expression but, as commented by @TimBiegeleisen, this is a recipe for disaster... Your best option is to rework the way you consume the JSON (to start with :,why are the spaces a problem ?)

Upvotes: 1

Related Questions