在Postgres 9.6中的json列中查询属性数组内的数据

Question

我有一个表说类型，它有一个JSON列，说位置看起来像这样：

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

表中的每一行都有数据，并且都有“type”：“state”。我只想从表中的每一行提取“type”：“state”的值，并将其放入一个新列中。我问了几个问题，比如：

• 查询JSON列中的数组元素
• 用于在JSON数组中查找元素的索引
• 查询JSON类型内的数组元素

但无法让它工作。我不需要查询这个。我需要这个专栏的价值。如果我错过了什么，我事先道歉。

Answer 1

我主要使用Redshift，其中有一个内置函数来做到这一点。所以如果你在那里，请检查一下。redshift文档

Postgres似乎有一个类似的功能集：https://www.postgresql.org/docs/current/static/functions-json.html

我认为您需要将三个功能链接在一起才能实现这一点。

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

我尝试的是按键名提取json，然后是按索引提取json数组（如果您的示例与完整数据一致，则总是第一个），最后是按键名提取另一个json数组。

编辑：让它为您的示例工作

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

返回“CA”

第二次编辑：嵌套查询@McNets是正确、更好的答案。但在这次潜水中，我发现你可以在Postgres中嵌套查询！多酷啊！

我将json作为文本字段存储在虚拟表中并成功运行了这个：

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

Answer 2

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在这里