CODEWITHSUNDEEP
sqlarraysjsonpostgresqlpostgresql-9.5
Jongz Puangput
Jongz Puangput

Reputation: 5637

JSONB column does not exist

I got this error.

ERROR: column "name" does not exist LINE 6:

SELECT JS, location, location->>Name

                              ^ SQL state: 42703 Character: 278

from this query

WITH JsonTable AS
(
    SELECT 
    '[
        {
            "Id":1,
            "Name":"A01",
            "Address":"aaa",
            "SearchVector":null,
            "ParentLocationId":null
        },
        {
            "Id":4,
            "Name":"B-01",
            "Address":"bbb",
            "SearchVector":null,
            "ParentLocationId":null
        }
    ]'::jsonb AS JS
) 
SELECT JS, location, location->>Name
FROM JsonTable, jsonb_array_elements(JS) x (location)

How can I select JSON value?

Upvotes: 7

Views: 11313

Answers (1)

GMB
GMB

Reputation: 222382

You are missing quotes around the name of the JSON attribute that you want to select. Just like object keys must always be quoted when the JSON object is declared, they need to be quoted when they are accessed.

See the Postgres documentation for the JSON datatype and JSON Functions and Operators.

You would need to change this:

SELECT JS, location, location->>Name

To:

SELECT JS, location, location->>'Name'

Demo on DB Fiddle.

Upvotes: 13

Related Questions