I noticed that there are very few examples of Postgres json_array_elements() and hence, we have this small post published. While writing queries for JSON data field, I’d recommend you to use an online JSON viewer website like jsonviewer.stack.hu. This tool will help you see the structure of the content that you are working on. See the example below:
As you see above, json content can be represented in various object structures. We can see the content as object value or as array content. Certainly, we need different methods to be able to query array and object structures. Before showing the first SQL, let us underline a few assumptions:
- Database table name: publications
- Json data column name: properties
- And consider that publications table contains only one raw – which is above.
Now, let’s see this simple query:
SELECT properties->>’sortOption’ FROM publications;
Query above will return “subjectArena” value. Why? Because, we are referencing the values labeled via “sortOption” tag and since we have only one row in our table, “subjectArena” string value will be returned by the query. And yes, you should be careful with the syntax. ‘->>’ command is used for string reference, and the tag name should be stated between apostrophe characters. (properties->>’sortOption’)
sortOption and publisherTermsAccepted tags are part of the object structure, but how about the availableOrganizations section? This is an array structure and you cannot access this query via simple referencing. Therefore, we should use json_array_elements() function.
SELECT json_array_elements(properties->’availableOrganizations’) FROM publications;
And this query will return the following values:
As you might have already noticed, we refer to the array (availableOrganizations) via ‘->’ command. This command is used for object pointing, as ‘->>’ is used for string reference. See the document here for more operators: www.postgresql.org/docs/9.3/static/functions-json.html
Sercan Leylek / OSLO