How do you work with arrays and JSON data types in PostgreSQL?

PostgreSQL provides support for both arrays and the JSON data type. Here’s how to work with them:

1. Arrays: Arrays are an ordered collection of elements of the same data type. In order to use arrays, you’ll need to create a table with a column of type array. Here’s an example:

CREATE TABLE my_table (
  id SERIAL PRIMARY KEY,
  values INTEGER[]
);

To insert data into the array column, you can use the array constructor syntax:

INSERT INTO my_table (values) VALUES (ARRAY[1, 2, 3]);

To access the data in the array, you can use the array subscript syntax:

SELECT values[1], values[2], values[3] FROM my_table;

This will return the values 1, 2, and 3.

2. JSON data types: JSON data types allow you to store and manipulate JSON data within PostgreSQL. In order to use JSON data types, you’ll need to create a table with a column of type JSON. Here’s an example:

CREATE TABLE my_table (
  id SERIAL PRIMARY KEY,
  data JSON
);

To insert data into the JSON column, you can use the JSON syntax:

INSERT INTO my_table (data) VALUES ('{"name": "John", "age": 35}');

To access the data in the JSON column, you can use the JSON functions provided by PostgreSQL. For example, the following query will return the value of the name property in the JSON data:

SELECT data ->> 'name' FROM my_table;

This will return the value "John".

You can also use the JSONB data type which provide improved indexing capabilities and reduced storage space comparing to the JSON data type but require more processing power.