logo标签列表

在PostgreSQL 9.4或9.5中查询JSON对象的嵌套数组中的元素

arraysjsonpostgresql
4
{
  "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”的元素？

[tag:json.net] 是专门针对 .Net 库 Json.NET 的。由于您似乎没有使用这个库，我已经将该标签删除了。 - dbc
请提供一个有效的JSON值。据我所见，这个有多个语法错误，缺少双引号和逗号。并且为什么你在要求9.4或9.5的时候打上[postgres-9.3]的标签？ - Erwin Brandstetter
1个回答
6
-- 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;
谢谢，它运行良好。我有一个小问题：如果我们在数组内部有另一组元素。例如，courseID：{coursename：“计算机科学”，coursecode：“csc213”}，我们如何选择coursename和coursecode？ - R.Y.H
如果您有嵌套的文档，可以使用 -> 符号表示 - info_item ->'courseID'->>'coursename'。 - Dmitry S
我想选择包含信息的年份，我尝试了以下代码：WITH info_data AS ( SELECT enrolment_info->'info' AS info, enrolment_info->’year’ as yr FROM jsontesting t, json_array_elements(t.data -> 'Data' -> 'enrolmentInfo') AS enrolment_info ) SELECT years, 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, to_json(idata.yr) as years; @Dmitry - R.Y.H
当“info”数组不为空时，此查询正常工作，但在某些情况下，我有年份但没有信息，但我想要年份。例如： “enrolmentInfo”：[{ “year”：“2015”， “info”： []}] 在这种情况下，查询未返回该年份。如果“info”数组为空，如何返回该年份？@ Dmitry - R.Y.H
@R.Y.H 您能否请更新问题，提供额外的 JSON 结构？ - Dmitry S
@DmitrySavinkov 我有一个关于这个问题的后续问题。如果我必须按特定的courseID进行过滤，我该怎么做？例如 WHERE courseID LIKE %cs% .. 只需添加一个像这样的where WHERE infoitem->>'courseID' LIKE "%cs%"; 就不起作用。你能告诉我出了什么问题吗？ - suprita shankar
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接