{
"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 } ]
} ]
}
}
我已经将上述格式的JSON对象存储在PostgreSQL数据库中。每个对象都包含有关某个学生的注册信息。我有带有嵌套数组的复杂对象。我正试图选择“info”数组中的所有元素。我尝试使用以下查询:
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”的元素?