Update and Delete operations for jsonb (part 1 of 2)

Video in TIB AV-Portal: Update and Delete operations for jsonb (part 1 of 2)

Formal Metadata

Update and Delete operations for jsonb (part 1 of 2)
Providing some needed functions and operators for jsonb
Title of Series
Number of Parts
CC Attribution - ShareAlike 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 and non-commercial purpose as long as the work is attributed to the author in the manner specified by the author or licensor and the work or content is shared also in adapted form only under the conditions of this license.
Release Date
Production Place
Ottawa, Canada

Content Metadata

Subject Area
Postgres 9.4 introduced the new jsonb type, However, it is missing some functions, particularly for json composition, that are needed by many users. In this talk we present an extension that provides some of these functions, and work to incorporate the functions in 9.5. Operations include replacement and deletion of array elements and object fields, and composition by concatentation of objects and arrays. Arrays can also be concatenated with scalar values, and array elements can be replaced or deleted by counting from either end of the array. Thus we have the ability to use json arrays as queues and stacks, with basic push/pop and shift/unshift capability. A pretty print function for jsonb is also provided. We will also outline what work we think remains, and discuss possible ideas on how to make json composition more naturally expressed.
Point (geometry) Slide rule File format Multiplication sign Software developer Workstation <Musikinstrument> Moment (mathematics) Operator (mathematics) Planning Hecke operator Mereology Rule of inference Frame problem Type theory Goodness of fit Mathematics Computer animation Different (Kate Ryan album) Military operation Operator (mathematics) Extension (kinesiology) Resultant Exception handling Spacetime
Standard deviation Regulärer Ausdruck <Textverarbeitung> Binary code Data storage device Binary file Total S.A. Element (mathematics) Element (mathematics) Type theory Subject indexing Computer animation Personal digital assistant Revision control Subject indexing Data structure Extension (kinesiology)
Computer virus Slide rule Functional (mathematics) Game controller Multiplication sign Source code File format Price index Abstract syntax tree Parameter (computer programming) Function (mathematics) Mereology Field (computer science) Value-added network Element (mathematics) Power (physics) Array data structure Sign (mathematics) Positional notation String (computer science) Operator (mathematics) Set (mathematics) Integer Mathematical optimization Form (programming) Metropolitan area network Computer icon Default (computer science) Shift operator Graph (mathematics) Key (cryptography) File format Data storage device Subject indexing Array data structure Sample (statistics) Computer animation Hash function Finite difference Personal digital assistant Order (biology) Right angle Negative number Object (grammar) Quicksort Resultant Spacetime
Module (mathematics) Group action Sine Link (knot theory) Patch (Unix) Disintegration Sound effect Electronic mailing list Directory service Machine vision Element (mathematics) Formal language Subject indexing Goodness of fit Computer animation Query language Operator (mathematics) Energy level Extension (kinesiology) Form (programming)
Computer icon Metropolitan area network Computer animation Lecture/Conference Multiplication sign Web page Normal (geometry) Task (computing)
Metropolitan area network Frame problem Zoom lens Table (information) Weight Web page Computer-generated imagery Arm Inclusion map Computer animation Personal area network Normal (geometry) Summierbarkeit Task (computing)
Metropolitan area network Group action Table (information) Computer animation Weight Web page Normal (geometry) Mereology Task (computing) Chi-squared distribution
Computer animation Weight Multiplication sign Chi-squared distribution
as well as you probably know that might have noticed my name is not a major goal of and who unfortunately couldn't be here because of some visa problems so he's given some slides on and I'm gonna run through that is and I have some slides of my hand and the basically the the history of this development is that Dimitri right an extension which is which you can find that they called Jason Bx which can be used with the press because none of 4 on to provide some estimated operators for Jason we that we really didn't get time to do in the mind up for a time frame on and particularly things which will let you can change parts of Jason document instead of having to provide a whole new documents on Dimitri right right this and published the end up and I I helped him to do some of that and then I took that and adapted that somewhat for or uh to to to be building the post based on the then adjust the little little more and then after some review we will appear to John Eccles said to rewrite parts of it and got committed and then we had we ran into a couple of moments of controversy about about nevertheless I think we're actually ended up with a pretty good results on sound gonna run through those features and entry what we've got how many people here I have have used adjacent impose 100 years Jason Bay and has but still a fair and the way of the difference between destiny and Jason not everybody OK so basically the difference is that Jason B is stored in a way that they decompose format which is much faster to prices on where is just the but the point J. Song type is stored as a piece of text as Fabian including all white space and any time you need to do anything with it you need to read past the somewheres with station B is is because it's stored in with decomposed format there is no need to re in the thing and so on and we can do a heck of a lot more with and the general if processing Jason you want to be using Jason be rather than the plane just hot at the couple exceptions but that's generally the rule the
so that the Jason be things started as an extension by a ligand terrible and from plus a little and it started but as a and they started to develop a nest the 5 major thought there was a this was discussed and eventually we decided that rather than and Mr. what we really wanted was to have an adjacent type which uh which use this that structure because it users out much much much more familiar in general with J. Song them with the matchless standardized syntax of H 2 O the case so that came about and 1 of the things that we got with with the Jason binary type was that a certain searches were supported with duty indexes and as we saw with the 1 that's always a ligand Alexander's talk
be said but here's what we've got at the up to now we can you we can get an element of novelty path we can delete 1 would kind update 1 and we can add a new 1 so here the new
features of 1 of the things we do we also have provided is a way of formatting Jason B. because when we say when we decompose it will dissolve away all white space so if you just output playing Jason B. you did get this great big long string of stuff and so would provide a function which will format it nicely for you so that's this new feature the next feature is is this and Jason B. 6 and basically this will not set an and elements the 2nd parameter is a path in the 1st parameter and the 3rd parameter is what what it is that will be set so we can see here in this thing ligand this where and is now in the source document we can replace that now with an array of 1 2 3 and this result in 1 2 3 to now there's a there's actually a 4th parameter here which is what we would change the default since the mature these slides but the four-parameter would actually defaults to true now says that if that part doesn't exist at the last element particularly of Part doesn't exist then we're going to create so here there is no sea element in the source document and here we've created 1 if that parameter had been false instead of true we would not have done anything we would simply return the original document because the path doesn't exist yeah you the order the the ordering all in Jason B is canonical right so you have no you have no control over the physical order the keys are stored in but this this stored know you have an object wikis B and another 1 with is being and I know we stored identically but can be so the path is an array all of the elements it's a and an array of text and if if the if the target of the thing is an object that was taken as a uh a key field field name and if it's a uh an erase it's taken as an array index and we support the negative indexes which basically come back from the last element so if you of so minus 1 designates the last element in their right mind as to the 2nd last and so on so essentially you can do things like uh as we'll see later on you can do things like the right questions and that sort of and shift and so on so this here's
here's a here's another example where we got more complex path essentially we can replace this to here with 1 2 3 and you can see that happening and and here we're replacing the the last thing the last element of this array under b because we've got a minus 1 and so that turns into a for a again now we also have this function that we have a bunch of case lead the delay functions in fact we don't we haven't actually document addressing the delayed because in normal has this form we don't actually have a document of the most of the functions that underlie operators in which have an optimal we have a couple of delay operators as will see but nevertheless you can delete that here this text thing this must refer to a field in an object new consumers to adjust removes the ailment and if it's if you pass an integer it will delay that array element beginning negative indexing works there and forget the past then it will delete the element designated by the US so the operators for that for the for the path will see the path uh 1 in a minute but but it's full of time if you just give a text or a mutagen you just use the minus operator and we'll take way that that which is kind of nice that notation and using jestingly delayed to avoid any ambiguity we observe we're not showing that maybe 1 2 does it all 4 of sharing signal operators where it where you want to delete something the power is the same minds you say 1 minus hash at the pound sign up before and and that's because otherwise we run into else we run into ambiguity problems and you have to do the nasty costs and that sort of stuff whereas if we since we use a different operated 9 and you can you don't have to be that it is possible direct a literal with the past so this cost here is in fact unnecessary OK the final thing we've got is that you can actually concatenate a couple of objects you can get an array array with object and the object with an array of and some skylights stuff and basically the fairly much in the same way that you can concatenate stores of things like that can arise this is a shall I concatenation that there was some consider some discussion about whether or not it should be be but that's what we that's what we have on an item that is still on the to do this is to have a deep merge operation I'll be talking about that in the wall so there is another graph of the concatenation using the the the concatenated to buy us is the concatenation operator in the same way that it is with the razor stores some other objects streets with you the virus itself again yes so yes but a lot of that now that's that's what we did in in the history of the world yet there is no this is not going away but if we if we implement a deep merge operation they'll be other function or a different operator OK so that's the
future the articles in this city of the sin not missing things and this is basically the same role at uh delete this element from Matisse uh and this and the and the others still leading the common elements in effect and or an intersection operator which will basically find the elements in column common the groups that we talk we we discussed in the 2nd again and and possibly small uh and um Morgan syntax of enough so that we need terribly much to do there but on the other hand if you here for a link notes on talk some then there is this module which show that critical jails query it's it's available on GitHub it has a fairly elegant language somewhat like a tears query language which can be used for querying Jason B and it's got some some nice index support for quite a lot of that and so you can be expressed expressed quite complex queries quite well in this in this language of every minute of course if they get if we get to where they want to be then will be will have support better at the signal level rather than having to have this specialized language but meanwhile you know that's probably a good couple of years out at the best of today's queries probably your best way to go for now so that's
the conclusion of directories talk about it has it his his conclusion basically says we continue to work on stuff is going to keep adding stuff to probably to adjacent Bx and uh extension enter and you'll probably come out with a vision of a form there was some new facilities for was 95 of excuse me OK
all take some Christianity that you could you ask questions any time but that's not hello that's
what I to that so there is that as
part of the of between here and here and in the
case of if you go to my knowledge has become it's really really because this is the group that from so it's just
which was the most of the time but what we know it's going this way