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:

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.

Is that always in the first array element? Can there be multiple array elements with `type = state`? — , May 18 '17 at 20:32
Not within the same row. Every row will only have one attribute with type = state. — Alice, May 18 '17 at 20:50

score 7 · Accepted Answer · answered May 18 '17 at 20:45

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

ScottieB · Answer 2 · 2017-05-18T22:30:33.720

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

Thank you @a_horse_with_no_name, but I cannot rely on index. It can be at any location within the array. — Alice, May 18 '17 at 20:56

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

2 Answers2