Postgres json get first element of array

by

“`html

PostgreSQL JSON – Get First Element of Array

In PostgreSQL, you can use the -> operator or the ->> operator to extract elements from a JSON array.

Here are some examples:

    -- Consider a JSON column named 'data' containing an array
    CREATE TABLE example (
      id serial PRIMARY KEY,
      data json
    );
    
    INSERT INTO example (data)
    VALUES ('[1, 2, 3]'), ('["apple", "banana", "orange"]');
    
    -- Get the first element of the JSON array using the -> operator
    SELECT data -> 0 AS first_element
    FROM example;
    
    -- Output: 1, "apple"
    
    -- Get the first element of the JSON array as text using the ->> operator
    SELECT data ->> 0 AS first_element_as_text
    FROM example;
    
    -- Output: "1", "apple"

The -> operator returns the first element of the JSON array as JSON, while the ->> operator returns it as text.

Note that the index is zero-based, meaning the first element has an index of 0.

“`

Leave a comment