PostgreSQL – How to Extract Values from JSON Array Field

jsonpostgresql

I'm storing a lot of json from some websites on PostgreSQL 9.5 in the jsonb format. One of the fields is an array of Jsons that have the same information. Here is an example.

[
  {
    "text": "JIMIN",
    "indices": [
      16,
      22
    ]
  },
  {
    "text": "PUMA",
    "indices": [
      23,
      28
    ]
  }
]

I want to retrieve all the text values, that is, I want to make a query that gives me JIMIN and PUMA on this example. I can't find a way to accomplish that with the commons operators (like #>>). Is there a way to accomplish this?

Best Answer

You didn't provide any code, so I will create a sample table with one row and some queries to extract the information you need.

CREATE TABLE test (myfield jsonb);

Load data:

INSERT INTO test (myfield) VALUES ('
[
  {
    "text": "JIMIN",
    "indices": [
      16,
      22
    ]
  },
  {
    "text": "PUMA",
    "indices": [
      23,
      28
    ]
  }
]
'::jsonb);

To extract a specific item, let's say the text field of the second array item:

SELECT myfield#>>'{1,text}' AS text FROM test;

This will output:

  text 
 ------
  PUMA
 (1 row)

In order to extract all the text fields inside the array, a possible solution is:

SELECT (jsonb_array_elements(myfield)::jsonb)->'text' AS text FROM test;

The output is:

  text   
---------
 "JIMIN"
 "PUMA"
(2 rows)

Obviously this works with many records: each array item will be ported to one row for each array. This query is compatible with PostgreSQL 9.5. Look at the official docs to create a more efficient or versatile query ( https://www.postgresql.org/docs/9.5/static/functions-json.html ).

Related Question