Accessing an Array Inside JSON with a Postgres Query

Question

I have a table with a data_type of json that I need to query one of the properties inside of it.

This is what the data in the column looks like:

{
  "id": 7008,
  "access_links": [
    {
      "product_code": "PRODUCT-1",
      "link": "https://some.url"
    },
    {
      "product_code": "PRODUCT-2",
      "link": "https://someOther.url"
    }
  ],
  "library_id": "2d1203db-75b3-43a5-947c-8555b48371db"
}

I need to be able to pull out and filter by the product_code nested inside of the access_links.

I can get one layer deep by using this query:

SELECT
    courses.course_metadata -> 'access_links' as access_links 
FROM
    courses

This seems to get me into the column, but I can't query any further.

The output I receive from the query looks like:

[{"product_code":"PRODUCT-1","link":"https://some.url"},{"product_code":"PRODUCT-2","link":"https://someOther.url"}]

I've tried using the ->> and #>> operators, but they both complain about the array not starting with a {. Also worth noting that the column is a data type of JSON not JSONB, so the @> operator doesn't work.

What am I missing here?

Jayadevan · Accepted Answer · 2020-02-21 04:54:23Z

1

Does this help?

select 
json_array_elements (x->'access_links')->'product_code' as product_code
from 
(select '{
  "id": 7008,
  "access_links": [
    {
      "product_code": "PRODUCT-1",
      "link": "https://some.url"
    },
    {
      "product_code": "PRODUCT-2",
      "link": "https://someOther.url"
    }
  ],
  "library_id": "2d1203db-75b3-43a5-947c-8555b48371db"
}'::json x 
  )  as v 
  ;

product_code

"PRODUCT-1"
"PRODUCT-2"

answered Feb 21, 2020 at 4:54

Jayadevan

1,4342 gold badges16 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ian Over a year ago

Perfect! That was exactly what I needed. I had seen the json_array_elements, but couldn't find a use case that fit what I was trying to do.

Collectives™ on Stack Overflow

Accessing an Array Inside JSON with a Postgres Query

1 Answer 1

product_code

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

product_code

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related