Change Json text to json array

Question

Currently in my table data is like this

Field name : author

Field Data : In json form

When we run select query SELECT bs.author FROM books bs; it returns data like this

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

But I need selected data should be like this

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

Database : PostgreSql

Note : Please avoid PHP code or iteration by PHP code

sparrow · Accepted Answer · 2019-02-12 05:48:04Z

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

Collectives™ on Stack Overflow

Change Json text to json array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related