CODEWITHSUNDEEP
sqlnode.jspostgresql
Nicolas Kao
Nicolas Kao

Reputation: 333

How to select column with json without sending column name?

I'm setting up an API with Node and postgreSQL-node (pg), and when I query for the column in my data base that contains JSON it returns an array of objects with the columns name and the json I want to access.

My current query is: 

select jsondata 
from breweries

The output is:

[
    {
        "jsondata": {
            "id": 2,
            "name": "Avondale Brewing Co",
            "brewery_type": "micro",
            "street": "201 41st St S",
            "city": "Birmingham",
            "state": "Alabama",
            "postal_code": "35222-1932",
            "country": "United States",
            "longitude": "-86.774322",
            "latitude": "33.524521",
            "phone": "2057775456",
            "website_url": "http://www.avondalebrewing.com",
            "updated_at": "2018-08-23T23:19:57.825Z",
            "tag_list": []
        }
    },
    {"jsondata": {...}}, 
    {...}
]

My expectation is to get and array with the contents inside "jsondata" without the name of the column "jsondata", but I can't find a way to access it one level in my query.

Edit: Here is what I expect:

[
    {

            "id": 2,
            "name": "Avondale Brewing Co",
            "brewery_type": "micro",
            "street": "201 41st St S",
            "city": "Birmingham",
            "state": "Alabama",
            "postal_code": "35222-1932",
            "country": "United States",
            "longitude": "-86.774322",
            "latitude": "33.524521",
            "phone": "2057775456",
            "website_url": "http://www.avondalebrewing.com",
            "updated_at": "2018-08-23T23:19:57.825Z",
            "tag_list": []

    },
    {...}, 
    {...}
]

Upvotes: 0

Views: 724

Answers (1)

ravioli
ravioli

Reputation: 3823

I'm not sure what exact output you're expecting, but you can convert the array of dictionaries to rows and then return the jsondata values as individual rows using this:

SELECT jsonb_array_elements(jsondata)->'jsondata' 
FROM breweries

Is that along the lines of what you want?

SQL Fiddle

Upvotes: 1

Related Questions