What Does Postgres Json Data Type Looks Like

An example using atmospheric condition information provided by the API at OpenWeatherMap

Introduction

Desire to know how to use PostgreSQL' s great functionality to work with JSON objects? Read on! Originally introduced in version 9.2, this feature was greatly enhanced in version 9.3 and nosotros will look at the operators that come with the JSON datatype. We assume that you have an installed version of PostgreSQL nine.3 or higher if yous want to work through the examples shown in this post.

Novelties

The main attribute of the JSON back up is the new datatype and its supplementary functions and operators. In this post, nosotros focus on the operators since they form a strong pillar for typical piece of work with JSON objects. Here is a concise list of the well-nigh important new features:

new datatype: json
new operators: ->, ->>, #> and #>>
new functions: json_array_length, json_extract_path, json_array_elements and many more

The full list of JSON specific functions tin be found at:

https://world wide web.postgresql.org/docs/9.3/static/functions-json.html

General Usage of the Operators

Let's get a quick overview concerning the main usage and differences among the three operators. After that we will show you their usage in several examples.

The operators -> and ->> are piece of cake to use and allow you to signal to a specific kid chemical element within a JSON object. Both operators tin can be used either past using the name of the element that is of involvement, or past using an index when the electric current object is an array of elements. It is also possible to provide a dynamic path within a JSON object that we want to specify. For this purpose, the operators #> and #>> accept been added and we tin say, in terms of the resulting datatype, #> equals -> and #>> equals ->>.

Before we get to the examples, here is a list of the major aspects of the operators:

Operator ->

Allows you lot to select an element based on its name.
Allows you to select an chemical element inside an array based on its index.
Tin can be used sequentially: ::json->'elementL'->'subelementM'->…->'subsubsubelementN'.
Return blazon isjson and the result cannot exist used with functions and operators that require a cord-based datatype. But the issue tin be used with operators and functions that require a json datatype.

Operator ->>

Allows you to select an element based on its name.
Allows yous to select an chemical element within an array based on its index.
Cannot be used sequentially.
Render type istext and the result can be used with functions and operators that crave a string-based datatype. For the same reason, sequential usage of the operator is not supported.

Operator #>

Allows you lot to select an element based on its path inside the primary JSON object. The path tin can consist of chemical element names and array indexes, depending on what is needed.
Tin can be used sequentially: ::json#>'{elementname1,elementname2,index1,index2}'#>'{elementname3}'.
Render type isjson and the issue cannot be used with functions and operators that require a string-based datatype. Just the result can be used with operators and functions that crave a json datatype.

Operator #>>

Allows y'all to select an element based on its path within the main JSON object. The path can consist of element names and array indexes, depending on what is needed.
Cannot be used sequentially.
Return type istext and the upshot tin can be used with functions and operators that require a cord-based datatype. For the same reason, sequential usage of the operator is non supported.
Basically, you can create a 'chain' of -> and #> operators, provided that yous bespeak to valid elements and indexes. Such a chain can terminate with each of the four operators. The concluding 1 determines whether the upshot can be used every bit input for other specific functions.
Note that if you want to use the result in combination with a role or operator that requires a text datatype, you take to employ ->> or #>> as the last ane in the concatenation. This might be important if you lot have a WHERE clause or a sub statement that refers to the upshot of the json operators. Permit us continue with some examples to come across these operators in action.

Sometimes I meet ::json and sometimes non, what is the difference?

This is an easy 1. The operators ->, ->>, #> and #>> require the input datatype json. If PostgreSQL tin implicitly convert the parameter to json then you can omit ::json. If you cannot guarantee that the casting is e'er performed in your statement, you pass ::json and the operators tin be used. When in doubt, employ the ::json prefix before the first json operator. The following operators, if you are chaining them, neither require nor support this syntax.

Query Examples

Insert sample data into a new database

First of all, we demand some data which nosotros can use for the exploring purpose. We will use weather data provided past the API at OpenWeatherMap. It may not be the best data in terms of a sophisticated use instance but it will be more than enough to get to grips with the operators considering the data contains some basic complexity.

Our interest lies in the data four five of the improve known English language cities London (THE London), Sheffield (Snooker earth title), Liverpool (the Beatles and Mersey Beat Generation), Manchester (this is for the football fans) and Leicester (the delicious Red Leicester cheese). Beneath is the link to become an API fundamental weather account, which is necessary to go access to weather condition API:

https://home.openweathermap.org/users/sign_up

If you want to use the same information as in this article, you lot can execute the following sql statements. Please create a database for testing purposes first. The post assumes that this has already been washed. An alternative way is presented after the statements in one argument that creates the new tabular array and fills information technology with data in one pace.

CREATE TABLE public.json_table (myjson json); INSERT INTO public.json_table VALUES('{"coord":{"lon":-0.xiii,"lat":51.51},"sys": {"type":3,"id":98614,"message":0.0218,"land":"GB","sunrise":1427693969,"dusk":1427740219},"conditions": [{"id":803,"main":"Clouds","description":"broken clouds","icon":"04d"}],"principal": {"temp":6.99,"humidity":57,"pressure":1014.9,"temp_min":5.56,"temp_max":ten.3},"current of air": {"speed":0.7,"gust":1.7,"deg":315},"pelting":{"3h":0},"clouds": {"all":64},"dt":1427699997,"id":2643743,"name":"London"}'); INSERT INTO public.json_table VALUES('{"coord":{"lon":-one.47,"lat":53.38},"sys":{"blazon":3,"id":38764,"message":0.0121,"country":"GB","sunrise":1427694192,"sunset":1427740640},"weather":[{"id":802,"principal":"Clouds","clarification":"scattered clouds","icon":"03d"}],"primary":{"temp":four.64,"humidity":75,"pressure":988,"temp_min":4.44,"temp_max":5},"wind":{"speed":3.08,"gust":7.71,"deg":226},"rain":{"3h":0.03},"clouds":{"all":36},"dt":1427699954,"id":2638077,"proper name":"Sheffield"}'); INSERT INTO public.json_table VALUES('{"coord":{"lon":-2.98,"lat":53.41},"sys":{"blazon":iii,"id":10567,"message":0.0519,"country":"GB","sunrise":1427694552,"dusk":1427741005},"weather":[{"id":800,"primary":"Articulate","clarification":"Sky is Clear","icon":"01d"}],"principal":{"temp":viii,"humidity":67,"pressure":m,"temp_min":3.33,"temp_max":17.78},"air current":{"speed":4.63,"gust":vii.2,"deg":282},"rain":{"3h":0},"clouds":{"all":0},"dt":1427700074,"id":2644210,"proper noun":"Liverpool"}'); INSERT INTO public.json_table VALUES('{"coord":{"lon":-two.24,"lat":53.48},"sys":{"blazon":iii,"id":28022,"message":0.0377,"country":"GB","sunrise":1427694371,"sunset":1427740831},"weather":[{"id":801,"main":"Clouds","description":"few clouds","icon":"02d"}],"main":{"temp":4.66,"pressure":1004,"temp_min":3.33,"temp_max":vi.11,"humidity":89},"air current":{"speed":3.six,"gust":5.65,"deg":268},"rain":{"3h":0.0099999999999998},"clouds":{"all":24},"dt":1427700074,"id":2643123,"proper noun":"Manchester"}'); INSERT INTO public.json_table VALUES('{"coord":{"lon":-1.thirteen,"lat":52.64},"sys":{"blazon":three,"id":184635,"message":0.9267,"country":"GB","sunrise":1427694152,"sunset":1427740519},"weather":[{"id":802,"master":"Clouds","description":"scattered clouds","icon":"03d"}],"main":{"temp":iv.92,"humidity":75,"pressure":1013,"temp_min":4.4,"temp_max":5.4},"air current":{"speed":ii.9,"gust":7.v,"deg":337},"pelting":{"3h":0.024999999999999},"clouds":{"all":36},"dt":1427700055,"id":2644668,"name":"Leicester"}');

Insert current data with SELECT INTO

Get your personal information from this URL after your created an API primal weather account and copy the resulting json into an SQL Editor from pgAdmin. You tin can insert the total json and make use of thejson_array_elements function.

SELECT value every bit myjson INTO public.json_table

FROM json_array_elements('{"cnt":five,"list":[…]}'::json->'list');

The statement takes the JSON object and extracts thelist element from it. As it contains an assortment with each index belongings data regarding one of the selected cities, we can use the predefined functionjson_array_elements that returns one row for each of the array elements. Due to SELECT INTO, all returned rows are inserted into the new tablepublic.json_table.

Examples in the SELECT Clause Now the time has come to finally look at some examples. We hop right into it.

Example SELECT

SELECT myjson::json->'name' as CityName

FROM public.json_table;

The query takes the object and searches for the chemical element name. This element's value is returned for each row in the table. Note that in the example the casting of the column to datatype json is performed.

Example SELECT with returned 'sub' json

SELECT myjson::json->'name' equally CityName,myjson::json->'coord' as Coordinates

FROM public.json_table;

We are getting slightly more circuitous past selecting two elements, both referenced via -> operator and their name, from the table. Note that the second column from the output is itself a JSON object which we will be targeting in the adjacent example.

Example SELECT with the 'sub' json strings

SELECT myjson::json->'name' as CityName,myjson::json->'coord'->'lon' as Longitude, myjson::json->'coord'->'lat' every bit Latitude

FROM public.json_table;

The example above shows the 'chaining' of the -> operator. The start usage has the::json prefix to ensure that the text is being handled equally json blazon and the sequent use of -> allows to reference an chemical element that is inside a JSON object which itself exists within a super object. It should be noted that the second utilize of -> has no preceding::json.

Example SELECT which returns array

At next, nosotros volition reference a specific array chemical element within an object. Looking at an extract from the entry for London, we have{…"atmospheric condition":[{"id":803,"main":"Clouds","description":"broken clouds","icon":"04d"}],…}. Conspicuously, the element weather contains an array, even if only of length ane. If we want to get farther into this assortment, we demand to specify that we are interested in the array chemical element at index 0 (indexes start with 0, non one).

SELECT myjson::json->'proper name' as CityName,myjson::json->'conditions'->0 as DetailedWeather

FROM public.json_table;

Clearly, nosotros only care about the core weather information and decide to select the elements main and description from the JSON object that is held in array element at index 0.

Example which addresses elements within this array

SELECT myjson::json->'proper noun' every bit CityName,myjson::json->'weather'->0->'main' every bit WeatherShort,myjson::json->'weather'->0->'clarification' equally WeatherLong

FROM public.json_table;

Above case also shows how the combination of chemical element names or indexes tin can be used to navigate to every part of a JSON object. Speaking of, or rather writing about, paths, we can also use the #> operator to achieve the aforementioned goal. Only that the syntax is slightly different.

Example SELECT with specific element referenced by the #> operator

SELECT myjson::json#>'{name}' as CityName, myjson::json#>'{atmospheric condition,0,primary}' as WeatherShort,myjson::json#>'{weather,0,description}' every bit WeatherLong

FROM public.json_table;

No deviation is in the outcome but some might adopt this way to betoken to distinct elements. In each path description (delimited by { and }), integer values are considered every bit index numbers and strings as element names; like -> and ->>.

In all these example the concluding occurrence of -> could have been swapped easily with ->> and no error would occur. To fully empathize their deviation, nosotros will endeavour to filter data in the adjacent two examples.

WHERE clauses with JSON

At best we repeat a part of the statement from higher up regarding the -> operator:"Return type is json and the outcome cannot be used with functions and operators that require a string-based data type". So we might be interested in all cities that have cloudy weather. We might try something similar the post-obit:

Incorrect

SELECT myjson::json->'proper name' as CityName

FROM public.json_table

WHERE myjson::json->'weather'->0->'main' = 'Clouds';

An error is thrown by PostgreSQL and reads:Error message:

Fault: Operator does not: json = unknownLINE 3: WHERE myjson::json->'conditions'->0->'master' = 'Clouds';

This tells us that the result of myjson::json->'weather'->0->'principal' is of type json but the = operator does non support json. If nosotros supervene upon the final -> by ->> so the result is not an JSON object merely a simple text that supports =. Now it should be clear when to use an operator that returns json and when to apply one that returns text. The aforementioned applies to #> and #>>, if we want to re-use the returned result.

Correct

SELECT myjson::json->'name' every bit CityName

FROM public.json_table

WHERE myjson::json->'weather'->0->>'chief' = 'Clouds';

Utilize ->> instead of -> when the returned values shall be re-used with ordinary operators and functions!

Some other, slightly more circuitous WHERE

SELECT myjson::json->'name' as CityName,myjson::json->'main'->>'temp' as Temperature

FROM public.json_table

WHERE myjson::json->'weather'->0->>'principal' = 'Clouds'AND Cast(myjson::json->'principal'->>'temp' as float) > five;

Our second case with WHERE clauses cares most all cities that take cloudy conditions but more than 5°Centigrade. As the API link above contained the part '&units=metric', the temperatures are provided in °C. Here is the consequence from the query:

We will have a concluding example showing the utilize of our json operators within a GROUP BY clause. GROUP Past with JSON operators

Grouping BY needs the ->> or the #>> operator in the last place of the chain club to being capable of grouping. The following example groups past type of weather and returns the number of cities, respectively.

SELECT myjson::json->'weather'->0->>'main' as WeatherType,COUNT(*) as AffectedCities

FROM public.json_table

GROUP By myjson::json->'weather'->0->>'principal'

The counterpart with #>> is

SELECT myjson::json#>>'{atmospheric condition,0,primary}' as WeatherType,COUNT(*) as AffectedCities

FROM public.json_table

Grouping By myjson::json#>>'{weather,0,main}'

Unfortunately, all good things come up to an end, and so does this blog post. Nosotros hope that you're now well-equipped to use the bones operators to navigate through JSON objects in order to select or reference whatsoever particular element. PostgreSQL also provides a skilful deal of functions to increase the capabilities of working with and building JSON type objects even further. However, the most mutual usage scenarios when data is already bachelor were covered in this article and the examples accept shown the differences and usages of the operators. We encourage all our readers to try to make the near of the functionalities that come with PostgreSQL.