Question 272991 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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;
Tags:

2

Answers


  1. 0

    With a convoluted JSON structure as this, I would use a JSONPATH query:

    SELECT *
FROM jsonb_path_query(
        /* your JSON goes here */,
        '$.**.fullname'
     );
    Login or Signup to reply.
  2. 0

    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).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search