Search code examples
jsonpostgresqljsonbjsonb-array-elements

Problem with JSONB functions retrieving arrays


In a table called temporay_data with a data field called temporary_data too, filled with this JSON structure

{
 "FormPayment": {
        "student": [
            {
                "fullname": "name student1 ",
                "rate": 210,
                "meal": 7,
                "mealValue": 175,
                "finalValue": 385,
                "role": "student",
                "willPay": true
            },
            {
                "fullname": "name student2",
                "rate": 210,
                "meal": 7,
                "mealValue": 175,
                "finalValue": 385,
                "role": "student",
                "willPay": true
            },
            {
                "fullname": "name student3",
                "rate": 210,
                "meal": 7,
                "mealValue": 175,
                "finalValue": 385,
                "role": "student",
                "willPay": true
            }
        ],
        "advisor": [
            {
                "fullname": "name advisor",
                "rate": 210,
                "meal": 7,
                "mealValue": 175,
                "finalValue": 385,
                "role": "advisor",
                "isParticipant": "yes",
                "willPay": true
            }
        ],
        "coadvisors": [
            {
                "fullname": "name coadvisors 1",
                "rate": 210,
                "meal": 7,
                "mealValue": 175,
                "finalValue": 385,
                "role": "coadvisor",
                "isParticipant": "yes",
                "willPay": true
            },
            {
                "fullname": "name coadvisors 2",
                "rate": 210,
                "meal": 7,
                "mealValue": 175,
                "finalValue": 385,
                "role": "coadvisor",
                "isParticipant": "no",
                "willPay": false
            }
        ]
    }
}

I need to select all fullnames, I know thats is an array in json. I tried the code above (and various others)

SELECT elements->>'fullname' as fullname
FROM (
    SELECT jsonb_array_elements(temporary_data->'FormPayment'->'student') as elements
    FROM temporary_data
) subquery;


and return this error 

ERROR:  function jsonb_array_elements(json) does not exist
LINE 31:     SELECT jsonb_array_elements(temporary_data->'FormPayment...
                    ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 687

Jsonb is istalled and functions jsonb_array_elements and jsonb_array_elements_text are installed.

I've tried all above

SELECT elements->>'fullname' as fullname
FROM (
    SELECT jsonb_array_elements(temporary_data->'FormPayment'->'student') as elements
    FROM temporary_data

    UNION

    SELECT jsonb_array_elements(temporary_data->'FormPayment'->'coadvisors') as elements
    FROM temporary_data
) subquery;
</code>
<code>
SELECT jsonb_array_elements(temporary_data->'FormPayment'->'student'->'{fullname}'::jsonb[]) as elements
FROM temporary_data;
</code>
<code>
SELECT jsonb_array_elements(temporary_data->'FormPayment'->'student')->>'fullname' as fullname
FROM temporary_data;

Solution

  • ERROR: function jsonb_array_elements(json) does not exist

    The solution is trivial:
    Either use the function json_array_elements() or pass in jsonb (instead of json).