Hello! PostgreSQL has built in tools to parse JSON data. This is going to be a how to article about Postgres Parse JSON. I will show you how to parse a json using PostgreSQL.

Let’s get back to work.

Parsing a Simple JSON using PostgreSQL

Let’s assume we have a very simple JSON as:

{"name": "Open Base Systems", "url": "https://openbasesystems.com"}

If you want to use PostgreSQL to select data from a JSON object as simple as above you can use query:

select '{"name": "Open Base Systems", "url": "https://openbasesystems.com"}'::json->'name' as name;

Query above will return you:

You can store JSON string data in a column and use the same method to choose a field from that column.

Parsing Simple JSON Stored in a Table

Let’s create the table first:

CREATE TABLE public.jtest (
	id int NOT NULL GENERATED ALWAYS AS IDENTITY,
	jdata text NULL,
	CONSTRAINT jtest_pk PRIMARY KEY (id)
);

Insert data to the table:

insert into jtest (jdata) values ('{"name": "Open Base Systems", "url": "https://openbasesystems.com"}');

And query json data in the table:

select jdata::json->'name' as name from jtest  

Postgres will parse json value in the table and return you the following:

Postgres Parse More Complicated Objects

Let’s change inserted data in the sample table.

insert into jtest (jdata) values ('{"name": {"corp": "Open Base Systems", "Legal": "Open Base Systems"}, "url": "https://openbasesystems.com"}');

You can see, we have a nested object in the data we store in the table now. And we can query a nested object as following:

select jdata::json->'name'->'Legal' as LegalName from jtest 

Parsing a JSON Array

Like in other functions, PostgreSQL’s design helps us to speed up our way to complete tasks, like in any programming language you can use indexes to access to data in a json array.

Clear the data in the sample table and insert a new row.

insert into jtest (jdata) values ('{"name": ["Yigit", "Firuze", "Jack", "Daniel"]}');

And query the table with a little modification:

select jdata::json->'name'->2 as Name from jtest  

Query above will return third item in the array.

To Sum Up

Parsing JSON in PostgreSQL involves extracting meaningful data from JSON-formatted text stored in your database. PostgreSQL offers powerful tools like the jsonb data type and operators like -> and ->> to navigate and access specific values within complex JSON structures. You can parse JSON by:

1. Accessing top-level key-value pairs: Use jsonb->'key' to directly retrieve values associated with top-level keys.
2. Iterating through arrays: Utilize jsonb_array_elements() to loop through each element in a JSON array and access its properties individually.
3. Unnesting objects: Employ json_each() to unpack nested objects into separate rows, creating new columns for each key-value pair.

Remember, for optimal performance, use the jsonb data type instead of json and choose the most efficient parsing method based on your JSON structure and desired outcome.

You can refer to PostgreSQL Documentation About Parsing JSON for more information.

Keep In Touch

We’d Love Your Feedback!

If you found this article helpful or have any thoughts to share, please feel free to like, comment, and share your valuable feedback. We appreciate your input and are always eager to hear from our readers!