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:

raw-json
Raw Json Data
json-structure-view
Structured Json Content

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:

123

456

789

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s