Logo TIB AV-Portal Logo TIB AV-Portal

NoSQL as Not Only SQL

Video in TIB AV-Portal: NoSQL as Not Only SQL

Formal Metadata

NoSQL as Not Only SQL
Title of Series
CC Attribution 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Release Date

Content Metadata

Subject Area
A deep walk through PostgreSQL JSON features with data examples. This does include the new and shiny PostgreSQL 9.5 JSON features.
area Slides goods services Computer animation notation communication time interactive objects
web pages implementation functionality Open Source time sources ones part subset elements versions different string operations extent man position form area Super moment storage Transactional completion applications raids Types Computer animation case statements archive life Results since record
track functionality loss part computational number versions web mathematics Office model extent area relation key unique cellular Databases completion Types Indexable words Computer animation case objects band table Results
words Computer animation key breadth bracket expression statements sort Recursive table form
track Actions studies time sets student Privacy Arrays causal rates structure errors area standards bracket Development expression Dampfes bits completion processes Computer animation environment CIO sort table Results record
injectives flow functionality relation views time unit diffusing transfer computational elements Types words structured data Computer animation hypermedia case topology data fusion selection sort record
track functionality relation views workstation sets independent information connections mathematics words Computer animation case topology string orders statements selection procedure table errors absolute values Results
script choice functionality studies Java sets bits functions part information computational mathematics Dogs Computer animation operations statements procedure
Computer animation operations
voting Computer animation files rates Databases structure sort table heads several record second
time moment scans plan Databases raster part energy theoretical product Indexable category Computer animation rates Average different calculations sort table report record
Indexable Computer animation Blog traction
category Computer animation robotics sort Board
relation Computer animation views part mature
Computer animation
good morning everyone and welcome to the 1st talk today here in Dallas area uh on and like come to frost come um my name is stephanie studying time working at talk slides and so what we are doing some crazy kinds of things because we most of you might have some in the clock we have so was about our service of flying because we're going inside entertainment so but we are doing it on all devices which doesn't mean we have flying Postgres and that I mean we're saying an offense what is totally crazy sometimes but a lot of fun so that's about me and ask what's evolved to today as posters and Jason so 1st of all those guys this JavaScript Object Notation simply like that invented for JavaScript but now widely used for communications and the interaction between between education and all these kind of things we don't have to care about
encoding that the complete different things for example compared to which a man or was things she is because it's defined as you recalled but most implementations you should give a few debates a subset of recall but well good enough for data are exchange that applications but not only there it's also used to exchange data between different API as the tide ought to be called for very part use there currently we do have to stand up there is this 1 by Douglas Crockford and then there is this a command the 1 by Douglas Crockford is very funny because it's only when you print it it's only 5 pages that's a whole definitions definition and the other fun it's really human readable form the liberal arts season specs and when you ever took a look at CD standouts of all SQL uh is always facing a lot of pain to read it because it's not very very human readable and understandable but that 1 is really key here in this is the thing about it and that makes it easy so it's imposed as we're using the 1 that is implemented by the artist transport so the that's about that Jason that
archives and source codes are available since version 9 go to work on that which is from year for gold so we have it for 4 years now and then there was an extension of the Song b Song listening is originally done by among and there is an extension of edible rounded up since 2000 certain which were played a lot worth at the time being because there was better than the Jason implementation that Postgres had itself and on the other hand it was completely of the same implementation as it was a moment of the so I played a lot with the media or at the time being and to I also change me here or not use more money me because I wanted to profit for a complete yesterday and I use this piece and other type and posters was some hiking but was in the end it was running but it was only for my doesn't find never published that because it was a lot of since inside was just could it be possible to do it so then it came out that so Jason D. was invented Jason B is done in 9 don't form and so it's completely different to the ones that we had before so it's compressed Jason which is 1 of the most important things when it writes the data a way you don't see any Jason that is safe on this it's 40 transactional does mean you can never send you can do with PostgreSQL as a compliance does also work here with Jason and you can use it for up to 1 he gigabytes per future so 1 record 1 gigabyte curfew approaches fall Jason and usually so now we go to the jason functions that are available so this 1 is very funny because it something really dust what what its name to roll to adjacent so you good work usually as great statement thank you which try the complete results neutral as days death with one function we lived go them post does have every soul which is very very good that we are able to use air raids and that makes life easier insights about base because we can handle them inside and stores them but you can also export areas as Jason also with just 1 function calling the function and it does for its name Jason be to record said is of the of defeat of that 1 because you can use case would be to return a string data and work on it again like you worked with the other things and parents were fault any problems if you have decided probably we have several operators so in every element is just the returned and in that way in which OK the you can also that the position was way and that this is the area element by name so
cannot for you to find and you know the name we
had objects inside Jason you can also get your object and you can get value at a certain parts
so just give me in an area this kind of value which I just want to where there's the other thing that you should do with Jason to make it faster because we can use indexes on Jason beat it does not work with adjacent tho indexes are only available for Jason I nowadays is Jason type is only used it it's just for storing and not for accession it mostly later on because it's a it's not that fast and it's not compressed so it does convene a lot of more resources than Jason B. imposed if we have this nice named John index which is geographical index the fun is that we can and makes a complete Jason B. because Jason is mostly past and invest just watch here so we don't have to index values inside adjacent to the bound for trouble by Momel we just create an overall index and we can exist everything so that the next which makes it must much faster and you don't have to care about so it decayed in the but you can even do these crazy things s to create unique indexes on days and I don't know it is the does make sense just as possible so she needed you can do it now we come to you Jason functions with 9 . 6 as 1 new functions that that Jason insert that inside the cell value into adjacent be power and returning the complete change Jason um 9 of 6 is currently in beta about Sri so will be altered later this year probably wrong September so just before the European posters conference entirely this year I guess and for the case you can only see the commendations everything is in there with examples and it's also a nice extension to functions that we already do have the with 9 0 for we got a lot more functions in in Jason being X because we needed some that the who helped us for example Jason be pretty makes the Jason human readable so it's not for computers is for us that we are able to pass to Jason were var ious makes it easier when you coded Jason V. status for update or at values in societies of the then we have a new operator of for the concatenated if you can just put together a pool Jason be viewed and to get the results also score this concatenation and there is this the case where you can delete a king giver the key and the key will be removed all of the this will be if somebody's this starting with 9 . 4 there is an extension of a number PGx and there so that that's intimate all these 2 9 . 5 motions into 1 104 so they are even use the word in the older versions so what I'm using later on this are the Dallas office ways that is available on the web and the model will be using some amount so book reviews they are some years old but they have been available on the web so I use them to show some data are example of that did you want to take that is a table in the true database there are that are available so we are only using some of them which would be these 1 where it is the only in has 2 columns we use the then then I will know it's about music who would have guessed and we used tracks than the so to see what is possible way of relational data to work with that on Jason coming
about another commission does everybody know what a the key years if not please raise your hand OK so that's what to talk about it the keys are in length common table expressions and I will use them very often in my example and it's also known as with various because they stopped this topic very from this statement and thirdly extended from posters is that you can do in recursive within words and select all the data and hence here and and that's 1 and until it's less than 100 show you could select that also what fully select here that's cheese that you've defined up here and then you get a reside so it to make it clear it's another form of subselects but when you do that in the subselect you wouldn't have here inside with brackets and with everything and these kind of things are much more human readable and makes slightly better to understand as good statement that has some sort of subtle FIL let's see how it work so what
I'm doing here the I start with the West and here we are already with the common theme expressions so I give it a name that comes directly after the standard is yes and then I define what I want to do and that is give me the album my Easytrak IDE the name from the cable and I want to have to have this vapor return as Jason each world so I just give here of this role to Jason just the payment on that i've defined up here that's it the as a result you see we have Jason and it was some sort of not that so certain milliseconds for these is not so the advantages that you can use but you are currently this in tables and mix them up of Jason or return them as Jason and you can do with with inside in sensor about arrays and you have just available so the next 1 then would be that I extends cheering and make it a little bit more you to being was not the best 1 so so I'm going on with the track and then that the good thing that you can do with cause the common table expressions that is that you can change for that that's mean here I create the these define this text that we've already seen before and here I define another 1 day's non-aligned checks and there it is I select there the role to Jason as it has been done in the statement before and z is based on that 1 so and in the next 1 I'm going To select some I will data are and I select then here inside from the island table and go down and I joined both adjacent Jason so I can't excess every table that i've defined previously in the him expressions and so that makes it very handy to works that way and it's really better read will as to have a this subselects than in brackets and all this kind of things and what I'm doing here is so I'm using the Jason data that we've already seen from the last is that's the last results and here I am joining them together with a the I will so that I can do it i defined it here that I can accessories I will might be from that Jason that you've seen from when users the CIO the there's there's a few insights adjacent and so on as just time that reside as and so that I can use it just in the join and here I'm joining a table with Jason who but by somewhat data and In the end there comes standard the resided here on selecting from what i've defined here is what I want and I put in Area X on top of it so that does mean when we look we created every instead of the actors that confront and here we have lots of data are already defined as areas where we have not only is the active we have also the want and that only 1 we have all had albums in 1 world so what you can also do this instead of having that done by this way we can the way before we can also use that tool creates good of you so that we can better handle the data that is inside so these results so I'm going again here onto the bones there's that design again that you've seen before and tho the comes and the next come at him expression here where I selecting what we've seen here the complete Eric about the items groups and by the artist ID no I connected data with the others that sense because we have that so from there and the artist idea idea for can join them together very busy that way and what I'm now going on returning to complete resided as they should be instead of having an error rate was not a study that this thing before a creative use that we to complete reside as Jason B. so it created that you at the heart of look like so here we see complex the complete data set for Jason data are returned by his use of this crater privacy and it's still not that slow because of all actors that are in the database and we've all islands and avoid checks islands on tracks of 1 artist i encapsulated in 1 record all 1 student so the now let's see how it does really look like were 1st taste the present and think the big enough so that you the that much better and you see that Jason
be pretty that's really do a good job because it really creates Freddy Jason and the results were not with line-breaks worth invention so that it's really human readable so if you create Jason into are in the development process use these days to be pretty because it helps you to understand what they tell you do with time all at that what if you before everything in 1 road you don't find anything and with that you were a to find the structure c-structure identify the problems and are able to excessive data so what's the next
1 we are getting some data from that use and you're still using Jason method but what we're doing now when you have a look at at 1 now I'm could I have converted Jason data from view and I we convert that Jason data into relational data again by accessing the data fusion insights adjacent so what I'm doing here is I had 1st tainted with structured data as Christina and I switch that into Jason and all I use that data to return it again s relational data forward Jason it's the structured data Jason structured data that's still not a flow turning on might not that fast so we can also returned with more what we have here is a so I'm selecting against the data that we already had and I returned here every elements so that I can have the I'm entirely again as elements here and get beat up all their so how does it look like it is the so completely urination again so here you excessive data and just 3 times all of the data are again the because we have here we have everything we have to return them twice so that you have the data outside the error-rate relation hated again so that is the same as we have seen it before where flaws the relational data the where I must Anderson is in here with where I will UCI granted by me with metallic on this case so just stumbled upon it but we can do it also in a different way because there is this function Jason correct records that that's a transfer the data are selected here and at times it what's the definition that we give here in the yes part of and here you see a subselect because that is inside the Jason be records that function where gives the Clary that you want to select from 4 you see I get only the data for the artist ID word for as you 50 and what we see here is we spend has here the text which is the adjacent b but 3 times the rest of again as relational data so that we are able to access this right away any questions so far quote so going it from Walla again so but can see its so here I'm selecting some data are again from this Jason V. records that and to gets that data this clade that way so these are than the I'm idea tragedy is the trick name media type of whatever this and that is the MS Hallwood along the so is and it does have for whatever reason the unit price um it's just there so I have to mention that when when I read from the data data have to mention all the diffuse here that are inside that Jason beef you truly trying them otherwise you might run into a problem for you have to know what's inside the trees weren't doing knowledge some sort of crazy thing I'm creating a function for a trigger B. Curless the the computers very low here so that's the crazy
because what I wanted to show knowledge is that I can use updates the data interviews that the recently created so we remember that we had this you where we created Jason from relational data and so that was a very complex you because it's not state just connecting some tables but and posters you are able to make set you write about what you have to do is you have to write your own function for that and post support from the tree functions so I only updates and the artist here so what I'm going through is getting the Jason data comparing if there was any change if there was a change then I run an update on the original table and I keep it here as that because that will go much deeper will updating the imams and tracks says about a possible for this you have to use absolute witches nuisance 9 . 5 where you can insert or update uh data with 1 statement in posters and it's just that it's also only some error handling so if something goes wrong that we can display an error the the so the trigger was created but it's not attached as signals are
Pauli created a string of of order connection to the other which it's at sometimes very useful because the can reuse might trigger so 1 and doing you know is so high is trigger and let it run into that of of data on that you that you created recently and server FIL no it's attached for wherein the hand Noah's manipulate data 1st only visible thing so what I'm going to do here is I'm selecting Sunday fell out of the Jason and show it and I changed Sunday fell out with the Jason the set command for what I have to give here is so the fused with the data set in and here I naming what should be changed and here I write whatever this so I can just change data on the fly into a place text inside the Jason so that was the origin of the artist name and that is what i've replace it with and but that's not written in the dollar it's only inside the results with that you see right here all just getting an reside where we can change station days in the car on the fly so but known as we know how we could do it we update said Jason data and that carry updates of and the words in this just in the New Independent metallic I had to give it a name so let's see how it looks like and the the Jason has changed and the Jason comes through the new directly from the table so just in case that you don't believe me that is the access to the table and i've written truce view from Jason through the paper back so your even able to change your Jason they tell that you created on relational data and put it back into the origin tables as it takes some effort of course to write all this talk procedures um I've used the stand that language that if you really use that as the the GPL a
square but your statement to lose a lot of other languages so Python is available at java script is available for support and for procedures of functions in a simple stressful that the choice of what you do and reasoning we
have changed our data with so the function that the seed up here again where which was up so here I used the data the Jason V. says to replace that inside but there is as I said that we have another 1 there is he concatenating of operator and a concatenating operator you can reuse it for the same as what we have get with the Jason be set because if there's a change in in the key value inside it replaces it it overrides that everything that you will be extending the Jason parts of the no changes are just over so that is what we see here so it's still the same as in the 1st the from example where I the have the other study the origin of a off the Jason can do whatever I want and also can give the well correct name with the other functions so now we use a concatenation here to write a data back to bring it back to the original name which is a function so that after work also with the concatenation FIL the trigger was executed hopefully let's see how it looks like in adjacent they for here with the done at the concatenation operator we've done the same thing as we did before with the set operator and as in concatenation operator is much more complex it takes a little bit longer to manipulate the data so when it's only changing data are used Jason be said if you also want to extend data or have changes and extending 4 adjacent then you then you can use so that the concatenation operator the so what they also is that I said from the there is the possibilities that so I can removed data was the minus operator what you see here and it also not changing the reside inside the territory that the dog they should Jason it's just a on the flight changing from data and you the output so that is a complete Jason as we had before and I use a pretty yeah again so that we are able to read it make it a little bit so status to the computer
value is that the have in the Jason and as you see here I can change some data
and the I can walk with that that and there's the the I will miss the others that there but yeah I was completely removed of the Jason and that's so far for the operation what you can do with relational data are and Jason Baker got to turn it upside down front and back around as you like and it's really fast and easy here what I'm
doing all as a going to create a table to data from this and book reviews for further creates a table the that's done only 1 feud inside that is that the adjacent if you the data is stored in in a file form of address Jason so I put the data that takes some 2nd because where even in 1998 on had several book reviews and the way that it took 7 seconds which is 20 and look for nearly 600 thousand records the fast enough and to imported into the database will want very slow we we lost copy the so let's see how it does look like we only takes a 1st record you and he is the the structure is some
sort of use dates whatever this volts ratings rating head for a vote and toric stuff inside grouping around of inside that that Jason so now we just
select some data from that Jason what I'm doing here is that I get the product tied and get the average a review of for the rating for the review and to see what the data is a ball energy would only by 1 category so I have also where inside the and for the calculating that they come from the Jason feuds inside use 1 table having this and got was to 1 of nearly 250 ms it's not that slope I think part it's OK for some sort of things 4 6 anatomical but no that's created an index that also takes obviously some time to create all the part because the past all of all adjacent menus inside so that table thank you for the 2nd floor of but for 600 thousand records that you only do it once and then you have excess through election all at the whole farce is no 8 ms so that index was way of creating so would you see it take the next really everywhere when you take a look at the explain plan you see that he just unique can right here the bitmap index scan and just use the index and to reduce the data are and to aggregated after what it's just of creating an index on this Jason stuff the that's going to that on get some more data on of detection with longer because that's calculation of ball the over the the old data are in the database so there are a lot of so we use with books that don't have a category we see here that these 1 and for some of the most some of the very and 1 of meaningful and here we have 2 categories the average rating and so we can even go over the whole data are and the trace the without doing whatever we want with this chase and and these kind of things is the reason why only be announced only this year which there no we know that we use in in February I think it was so that they know that they have null and B I too would fill all of the books B I too would normally be found effective at that time it turned out to be up 4 moment of use posters they created and foreign and out of about where you can access X so data and then you can access up 1 would be the data directly from Postgres and do attach every reporting tool that you would like to attach the the so what I'm doing here is i'm creating index on the product category just to show that it is possible it doesn't have very much because Jason index there are usually does the the beforehand so it's not faster than it was
before there was a difference when I did that flow of with 9 . 4 uh it turned out that the theory was fast off dietary Phoenix and manage 5 I tested it's a lot of times and uh that was really an increase in and and what post doesn't mean I'm not 5 in our own in performance so well
FIL without them 4 million blogs indexes Jason Paul stress for today any questions and thank you
that yes there was was you will be you this up so you know this so we have a high you know about the that this
due to the use of this you and can I kind the sort of is on this year's and so on you know how to use use of this you can vote on it and you can do it there's even 4 and out of our preferred foreign do I can do that all the board to external data which is set at a robot that to moral and you can put everything inside there with you need to unless as it you need to access that much of it can also create what I used here is sigh property data inside from that fired uh what
you can also do if you just links them
instead of copies them and then create maturity that views on top of it so then you can excessive daytime in relation away again and then has a lot of the other parts as days stored away lonely and when you create than the material used and then you have everything available that you can use for days the all functions and think so gives a lot of opportunities what you
can do with Jason data any more questions that thank you very much