Postgresql json select11/6/2023 JSON stands for JavaScript Object Notation. In this tutorial, you have learned how to work with PostgreSQL JSON data type and how to use some of the most important JSON operators to process JSON data more effectively.Summary: in this tutorial, you will learn how to how to work with PostgreSQL JSON data type and some useful operators and functions for handling JSON data. There are more PostgreSQL JSON functions if you want to dig deeper. SELECT json_typeof (info-> 'items'-> 'qty') The following query returns the data type of the qty field of the nested items JSON object. The following query return the data type of the items: SELECT json_typeof (info-> 'items') It can be number, boolean, null, object, array, and string. The json_typeof() function returns type of the outermost JSON value as a string. The following query returns all keys of the nested items object in the info column SELECT json_object_keys (info-> 'items')įROM orders Code language: SQL (Structured Query Language) ( sql ) json_typeof function To get a set of keys in the outermost JSON object, you use the json_object_keys() function. If you want to get a set of key-value pairs as text, you use the json_each_text() function instead. See the following statement: SELECT json_each (info) The json_each() function allows us to expand the outermost JSON object into a set of key-value pairs. PostgreSQL provides us with some functions to help you process JSON data. SUM ( CAST (info -> 'items' -> 'qty' AS INTEGER)),ĪVG ( CAST (info -> 'items' -> 'qty' AS INTEGER))įROM orders Code language: SQL (Structured Query Language) ( sql ) PostgreSQL JSON functions MAX ( CAST (info -> 'items' -> 'qty' AS INTEGER)), SELECT MIN ( CAST (info -> 'items' -> 'qty' AS INTEGER)), For example, the following statement returns minimum quantity, maximum quantity, average quantity and the total quantity of products sold. We can apply aggregate functions such as MIN, MAX, AVERAGE, SUM, etc., to JSON data. Notice that we used the type cast to convert the qty field into INTEGER type and compare it with two. WHERE CAST ( info -> 'items' -> 'qty' AS INTEGER) = 2 Code language: SQL (Structured Query Language) ( sql ) To find out who bought two products at a time, we use the following query: SELECT info -> 'customer' AS customer, WHERE info -> 'items' -> 'product' = 'Diaper' Code language: SQL (Structured Query Language) ( sql ) ![]() For example, to find out who bought Diaper, we use the following query: SELECT info -> 'customer' AS customer We can use the JSON operators in WHERE clause to filter the returning rows. And then info->'items'->'product' returns all products as text. ORDER BY product Code language: SQL (Structured Query Language) ( sql )įirst info -> 'items' returns items as JSON objects. For example, the following statement returns all products sold: SELECT info -> 'items' -> 'product' as product The following query uses the operator -> to get all customers in form of JSON: SELECT info -> 'customer' AS customerįROM orders Code language: SQL (Structured Query Language) ( sql )Īnd the following query uses operator -> to get all customers in form of text: SELECT info -> 'customer' AS customerīecause -> operator returns a JSON object, you can chain it with the operator -> to retrieve a specific node.
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |