Query data inside an attribute array in a json column in Postgres 9.6

Question

I have a table say types, which had a JSON column, say location that looks like this:

{ "attribute":[
  {
    "type": "state",
    "value": "CA"
  },
  {
    "type": "distance",
    "value": "200.00"
  } ...
  ]
  } 

Each row in the table has the data, and all have the "type": "state" in it. I want to just extract the value of "type": "state" from every row in the table, and put it in a new column. I checked out several questions on SO, like:

• Query for element of array in JSON column
• Index for finding an element in a JSON array
• Query for array elements inside JSON type

but could not get it working. I do not need to query on this. I need the value of this column. I apologize in advance if I missed something.

Answer 1

create table t(data json);
insert into t values('{"attribute":[{"type": "state","value": "CA"},{"type": "distance","value": "200.00"}]}'::json); 

select elem->>'value' as state
from t, json_array_elements(t.data->'attribute') elem
where elem->>'type' = 'state';

| state |
| :---- |
| CA    |

dbfiddle here

Answer 2

I mainly use Redshift where there is a built-in function to do this. So on the off-chance you're there, check it out. redshift docs

It looks like Postgres has a similar function set: https://www.postgresql.org/docs/current/static/functions-json.html

I think you'll need to chain three functions together to make this work.

SELECT 
   your_field::json->'attribute'->0->'value'
FROM
    your_table

What I'm trying is a json extract by key name, followed by a json array extract by index (always the 1st, if your example is consistent with the full data), followed finally by another extract by key name.

Edit: got it working for your example

SELECT 
  '{ "attribute":[
  {
    "type": "state",
    "value": "CA"
  },
  {
    "type": "distance",
    "value": "200.00"
  }
  ]
  }'::json->'attribute'->0->'value'

Returns "CA"

2nd edit: nested querying @McNets is the right, better answer. But in this dive, I discovered you can nest queries in Postgres! How frickin' cool!

I stored the json as a text field in a dummy table and successfully ran this:

SELECT 
  (SELECT value FROM json_to_recordset(
    my_column::json->'attribute') as x(type text, value text)
  WHERE
    type = 'state'
    )
FROM dummy_table