PostgreSQL – JSON_EACH Function
In PostgreSQL, the json_each function is used to expand a JSON object into a set of key-value pairs. This function can be helpful when you want to break down a JSON object into its individual components for further analysis or manipulation.
The json_each function returns a set of rows, each containing a key and its corresponding value from the JSON object. The basic syntax of the json_each function is as follows:
json_each(json_object)Here’s an example of how you might use the json_each function:
SELECT * FROM json_each('{"name": "Alice", "age": 28, "city": "London"}');In this example, the function would return three rows, one for each key-value pair in the JSON object:
diffCopy code
key | value---------------name | Aliceage | 28city | London
You can use the json_each function within a query to extract and work with individual elements of a JSON object. For example, you might use it to filter rows based on specific conditions within the JSON object:
SELECT * FROM your_table WHERE (SELECT value::int FROM json_each(json_column) WHERE key = 'age') > 25;In this query, we are retrieving rows from your_table where the value associated with the key 'age' within the JSON object in the json_column is greater than 25.