JSONB column does not exist

Question

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?

Necking

Answer 1

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.

Dynasty