yandex
loader

please wait

  • Bhanu Gupta Feb-12-2019 06:59:24 AM ( 5 days ago )

     

    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

  • Peter Jarvis Feb-12-2019 07:00:20 AM ( 5 days 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

Please login

Similar Discussion

Recommended For You