Jose Jose - 4 months ago 12
JSON Question

Query for array elements inside JSON[] field array type

I have tried to Unnest the JSON array with the function json_array_elements(), and tried to count the element of the array using json_array_length(field_name) not being successful I am using PostgreSQL 9.4.5.

I was looking to query the result for the element 'name' this is the data hold on the json type array field crew | json[].

crew": [
{
"workHours": "9",
"workers": "50",
"checker_rate": 100,
"rate": 150,
"name": "Ramona",
"last": null,
"boxRate": 2,
"checkTraining": false,
"editing": true,
"ix": 0,
"breakPay": 3.0833333333333335,
"trainingPay": 0
},
{
"workHours": "4",
"workers": "50",
"checker_rate": 120,
"rate": 160,
"name": "Ramon",
"last": "Rosas",
"boxRate": 2,
"checkTraining": false,
"editing": false,
"id": 1,
"breakPay": 1.5416666666666667,
"trainingPay": 0
}
]

Answer

Your problem stems from the incorrect use of the type json[]. A json array is a single json object and its type is json, not json[]. Example:

create table test (id int, crew json);
insert into test values
(1, '
[
    {
        "workHours": "9",
        "workers": "50",
        "checker_rate": 100,
        "rate": 150,
        "name": "Ramona",
        "last": null,
        "boxRate": 2,
        "checkTraining": false,
        "editing": true,
        "ix": 0,
        "breakPay": 3.0833333333333335,
        "trainingPay": 0
    },
    {
        "workHours": "4",
        "workers": "50",
        "checker_rate": 120,
        "rate": 160,
        "name": "Ramon",
        "last": "Rosas",
        "boxRate": 2,
        "checkTraining": false,
        "editing": false,
        "id": 1,
        "breakPay": 1.5416666666666667,
        "trainingPay": 0
    }
]');

The function json_array_elements() works as expected:

select id, elem->'name' as name
from test, json_array_elements(crew) elem;

 id |   name   
----+----------
  1 | "Ramona"
  1 | "Ramon"
(2 rows)