在PostgreSQL 9.4或9.5中查询json对象的嵌套数组中的元素 [英] Query elements in a nested array of a json object in postgresql 9.4 or 9.5
问题描述
{
"studentID": 1,
"StudentName": "jhon",
"Data":{
"schoolname":"school1",
"enrolmentInfo":
[{
"year":"2015",
"info":
[
{
"courseID":"csc213",
"school":"IT",
"enrollmentdate":"2015-01-01",
"finshdate":"2015-07-01",
"grade": 80 },
{
"courseID":"csc113",
"school":"IT1",
"enrollmentdate":"2015-09-02",
"finshdate":null,
"grade": 90 } ]
},
{
"year":"2014",
"info":
[{
"courseID":"info233",
"school":"IT",
"enrollmentdate":"2014-03-11",
"finshdate":"2014-09-01",
"grade": 81 },
{
"courseID":"csc783",
"school":"IT",
"enrollmentdate":"2014-01-02",
"finshdate":"2014-08-01",
"grade": 87 } ]
} ]
}
}
我已经在上述格式的Postgresql数据库中存储了json对象.每个对象都包含有关某个学生的信息以及入学信息.我在数组内部有嵌套数组的复杂对象.我正在尝试选择信息"数组内的所有元素.我尝试使用以下查询:
I have stored in postgresql database json objects of the above format. Each object consists of informations about a certain student with enrollment information. I have complex objects with nested array inside arrays. I am trying to select all the element inside the "info" array. I tried to use the following query:
with recursive x (info) as (select value->'info' from jsontesting r, json_array_elements(r.data->'Data'->'enrolmentinfo')
UNION ALL
SELECT (e).value->'courseID', (e).value->'school', (e).value->'grade',(e).value->'enrollmentdate', (e).value->'finshdate'
from (select json_each(json_array_elements (info)) e from x) p)
select * from x;
此查询无法正常工作,并且出现以下错误:无法在标量上调用json_array_elements".还有其他可用于提取嵌套数组"info"的元素的查询吗?
This query is not working and it is giving the following error:"cannot call json_array_elements on a scalar". Is there any other query that I can use to extract the elements of the nested array "info"??
推荐答案
-- assuming that jsontesting.data contains your JSON
WITH info_data AS (
SELECT enrolment_info->'info' AS info
FROM jsontesting t, json_array_elements(t.data -> 'Data' -> 'enrolmentInfo') AS enrolment_info
)
SELECT info_item->>'courseID',
info_item->>'school',
info_item->>'enrollmentdate',
info_item->>'finshdate',
info_item->>'grade'
FROM info_data idata, json_array_elements(idata.info) AS info_item;
这篇关于在PostgreSQL 9.4或9.5中查询json对象的嵌套数组中的元素的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!