Postgresql – How tonsert into with postgresql and JSONB

If you add all ColumnsNames (exclude the 'id' Column) after the Tablename, there will be Insert the serial automaticly like:

INSERT INTO animals(nm,typ,tvi,tvf) select nm,typ,tvi,tvf from json_po..... 

You can also add a DEFAULT Value in your Column, to set a Default Value if the column is not in the Insert Column-list.

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