Change Json text to json array

manpreet Best Answer 2 years ago

"[{\"author_id\": 1, \"author_name\": \"It happend once again\", \"author_slug\": \"fiction-books\"}]"

    [
        {
          "author_id": 1,
          "author_name": "It happend once again",
          "author_slug": "fiction-books"
        }
    ]

manpreet 2 years ago

The answer depends on the version of PostgreSQL you are using and ALSO what client you are using but PostgreSQL has lots of builtin json processing functions.

https://www.postgresql.org/docs/10/functions-json.html

Your goal is also not clearly defined...If all you want to do is pretty print the json, this is included.

# select jsonb_pretty('[{"author_id": 1,"author_name":"It happend once again","author_slug":"fiction-books"}]') as json;
                      json
-------------------------------------------------
 [                                              +
     {                                          +
         "author_id": 1,                        +
         "author_name": "It happend once again",+
         "author_slug": "fiction-books"         +
     }                                          +
 ]

If instead you're looking for how to populate a postgres record set from json, this is also included:

# select * from json_to_recordset('[{"author_id": 1,"author_name":"It happend once again","author_slug":"fiction-books"}]')
as x(author_id text, author_name text, author_slug text);

 author_id |      author_name      |  author_slug
-----------+-----------------------+---------------
 1         | It happend once again | fiction-books