json datatype

What would it do for you that a CLOB does not.  Among other things, a CLOB can be used for many types and JSON used for only one.  So, what is your gain?

So, I am asking what that optimization is.  What does one actually get for it.  Reforming a JSON object out of a string is hardly an expensive operation.

I'm not claiming there is no advantage.  I just want to know what that advantage is.  Might it, for example, provide a reason for an XML or DOCX type as well?

Why either?  Depends on what advantages are being claimed.  There are libraries for creating DOCX from ABL, so one might want to do something with the result.  Until we know the advantage, we don't know whether we want it.  I would claim that, if the only advantage is the cost of returning to a native document ready to process, the advantage is insufficient to justify a specialized type.

Huh?  No-undo variables are significant because, *cumulatively* keeping BI state on a large number of variables introduces BI overhead, i.e., disk I/O which impacts application performance.  What exactly are you doing with JSON objects ... JSON object which you have decided to pay the performance cost of persisting to disk and then find and reading again ... where the cost to reconstitute the JSON object itself more efficiently is going to make a meaningful difference to the performance ... again, noting that we are already talking in the context of a disk access?

I understand the use case.  The only piece I am unsure about ... and mind you, it is unsure, not active opposition ... is the need for a special JSON datatype vs stuffing the JSON in a CLOB.  If one sends it over the wire, one is just sending character data ... it happens to be in a format which is useful to the destination without special parsing.  Here, one is in the context of paying the price of a disk read, so a little ABL overhead is minor and can't one parse a longchar into a JSON object ready for use with one line of code?  So, where is the advantage?

Postgresql uses a Jason datatype, which can be indexed and searched on which is really powerful. all the features of mongodb, but with transactional support...
http://www.postgresql.org/docs/devel/static/functions-json.html

Indexing was one of the things that occurred to me as a possible plus, but I was interested in a real use case since I am not sure what kind of indexing one would apply to such a potentially arbitrary string.  Is it word indexing?  I can see it being highly desirable to word index a CLOB, but I don't know that making a separate JSON datatype would necessarily make that easier and I can think of a lot of uses for it besides that.  For uses like saving context across AppServer calls and such, I would think one would actually want separate fields to contain the user id, session id, etc. and those are easy to index.  So, the use case is still a bit fuzzy to me beyond the general attraction of a word index on any CLOB.

It's not just a word index search..

You can search for all records containing a key value pair, like customer=> foo

You can't do that with a word index

OK, Julian, that is the first indication I have seen of an actual benefit.  Now, to convince PSC development, what is the use case?  The use case I get for stuffing JSON into a DB is things like preserving context across sessions.  I can see wanting to index that by session id and the like, maybe a timeout value for when to preserve it, etc., but I would put those in separate fields.  What are you doing with these JSON objects that you would want to retrieve on key-value pairs?  Seems to me that implementing such a search feature would actually involve greater difficulty than allowing word search on a CLOB.  Couldn't one accomplish this with word index?

Mike, you are right, I am not getting the point.  I understand use cases for putting JSON data into a database for things like caching it across passes on an AppServrer, but no, I don't know what the use case is for 100,000 of them and wanting to search across it.  If one is storing a large amount of data and wanting index capabilities, why would one not put the data into regular fields?

I am not against you, just playing Doubting Thomas in making you present an actual use case like development would need before considering such a thing.

all I can say is that thousands of developers have a use-case for
using json in databases - even PSC's latest acquisition, Modulus, uses
MongoDB

There are several cases where I have a need for json-type data : for
example, interface files from external system may have some common
fields like agentid, name, address, balance, but often have non-common
fields

so, at the moment I create a table with agentid, name, address and
balance, but use a "data" clob field to store a json representation of
the rest of the data - rather than creating a table for each interface
(we have hundreds)

Now, I have a need to loop through some of this data, and find only
records with a certain key-value pair (which may or may not be several
levels deep). The only way of doing that , is by reading each record,
converting to json , and checking if the resultant json object
has("foo")

It would be *much* better to go

for each inputData no-lock where InputData.data has "foo=>bar"

instead of reading all the bloody records.

Julian



On 15 June 2014 18:50, Thomas Mercer-Hursh
wrote:
> RE: json datatype
> Reply by Thomas Mercer-Hursh
>
> OK, Julian, that is the first indication I have seen of an actual benefit.
> Now, to convince PSC development, what is the use case? The use case I get
> for stuffing JSON into a DB is things like preserving context across
> sessions. I can see wanting to index that by session id and the like, maybe
> a timeout value for when to preserve it, etc., but I would put those in
> separate fields. What are you doing with these JSON objects that you would
> want to retrieve on key-value pairs? Seems to me that implementing such a
> search feature would actually involve greater difficulty than allowing word
> search on a CLOB. Couldn't one accomplish this with word index?
>
> Mike, you are right, I am not getting the point. I understand use cases for
> putting JSON data into a database for things like caching it across passes
> on an AppServrer, but no, I don't know what the use case is for 100,000 of
> them and wanting to search across it. If one is storing a large amount of
> data and wanting index capabilities, why would one not put the data into
> regular fields?
>
> I am not against you, just playing Doubting Thomas in making you present an
> actual use case like development would need before considering such a thing.
>
> Stop receiving emails on this subject.
>
> Flag this post as spam/abuse.



--
Julian Lyndon-Smith
IT Director,
dot.r
http://www.dotr.com

"The bitterness of poor quality remains long after the sweetness of
low price is forgotten”

Follow dot.r on http://twitter.com/DotRlimited

I can see the need in a communication layer more than in the application database .. the problem there is less well fit to a relational data model to start with.  But, couldn't one solve the requirement with word index on clob simply by adjusting the word break rules?  The reason I keep coming back to that is that, while we don't have that either, it seems like a fairly simple extension of capability compared with a whole new datatype that had its own special functionality plus word index on CLOB could be used for a ton of things.

*much* better like in this will happen anyway behind the scene or?

if there is no index to support a query all records will be read anyway,
if a field contains json/xml someone will have to serialize/deserialize
the damn data... it can be deserialization on read while data is stored
in document format or serialization on read proven they come up with a
better storage option, or can just de-normalize the darn thing and keep
it in original format and also in a more appropriate storage format to
be used in queries.

if you want to skip that full table read the only way imho is to start
adding indexes on that json but given you have hundreds of interfaces
are you going to create that many indexes in one table (specifically one
that seems to get a considerable amount of inserts)?

guess you're not letting this table to build like a history table... if
you need that then go with a 'staging' area, process data out of it and
keep the history in some other place(s).


[collapse]On 06/15/2014 09:09 PM, jmls wrote:
>[collapse] From: jmls
> Post: Re: json datatype
> Posted in: OpenEdge General
> Link: http://community.progress.com/technicalusers/f/26/p/10765/40340.aspx#40340
>
> all I can say is that thousands of developers have a use-case for
> using json in databases - even PSC's latest acquisition, Modulus, uses
> MongoDB
>
> There are several cases where I have a need for json-type data : for
> example, interface files from external system may have some common
> fields like agentid, name, address, balance, but often have non-common
> fields
>
> so, at the moment I create a table with agentid, name, address and
> balance, but use a "data" clob field to store a json representation of
> the rest of the data - rather than creating a table for each interface
> (we have hundreds)
>
> Now, I have a need to loop through some of this data, and find only
> records with a certain key-value pair (which may or may not be several
> levels deep). The only way of doing that , is by reading each record,
> converting to json , and checking if the resultant json object
> has("foo")
>
> It would be *much* better to go
>
> for each inputData no-lock where InputData.data has "foo=>bar"
>
> instead of reading all the bloody records.
>
> Julian
>
>
>
> On 15 June 2014 18:50, Thomas Mercer-Hursh
> wrote:
>> RE: json datatype
>> Reply by Thomas Mercer-Hursh
>>
>> OK, Julian, that is the first indication I have seen of an actual benefit.
>> Now, to convince PSC development, what is the use case?The use case I get
>> for stuffing JSON into a DB is things like preserving context across
>> sessions.I can see wanting to index that by session id and the like, maybe
>> a timeout value for when to preserve it, etc., but I would put those in
>> separate fields.What are you doing with these JSON objects that you would
>> want to retrieve on key-value pairs?Seems to me that implementing such a
>> search feature would actually involve greater difficulty than allowing word
>> search on a CLOB.Couldn't one accomplish this with word index?
>>
>> Mike, you are right, I am not getting the point.I understand use cases for
>> putting JSON data into a database for things like caching it across passes
>> on an AppServrer, but no, I don't know what the use case is for 100,000 of
>> them and wanting to search across it.If one is storing a large amount of
>> data and wanting index capabilities, why would one not put the data into
>> regular fields?
>>
>> I am not against you, just playing Doubting Thomas in making you present an
>> actual use case like development would need before considering such a thing.
>>
>> Stop receiving emails on this subject.
>>
>> Flag this post as spam/abuse.
>
>


--
m.edu
keep it simple
http://www.ganimede.ro
http://ro.linkedin.com/in/marianedu
medu@ganimede.ro
mobile: +40 740 036 212
skype: marian.edu[/collapse][/collapse]

Let me play product manager here.  What I am getting here are descriptions of capabilities, not business use cases.  I understand the business use case for stuffing some JSON in the database temporarily as a cache and in that usage I am likely to provide, via another field(s), indexes to locate the desired piece of JSON easily, e.g., something like a session ID.  I get that one might want to have longer term store of documents in various formats, like an audit trail, but again one would expect primary access to be on some key fields.  

If one wanted to search the documents, one could certainly make a case for word index on a CLOB, but I don't see how JSON would stand out here as being a particularly preferred document format.   For history on something that is not inherently a document, I am not sure why one wouldn't parse to ordinary fields so that one had access through all the usual mechanisms, including SQL-based reporting tools which would be unlikely to support a JSON datatype as anything other than a string.

I understand that if one had a JSON datatype and this implied being able to index key-value pairs, that one could do fast searches on message content.  But, there are two things I don't understand about that.

One is how this capability would be any different than word index on a CLOB with the right word-break tools, a capability which would have a much, much broader utility than a JSON specific one.  Note in particular, that generalized search on key value pairs implies full inversion, like word index.   If there is only a limited number of keys on which one would ever search, then throwing the values into regular fields and indexing those is an immediately available capability supported by reporting tools.  I.e., by asking for full inversion on a JSON datatype, you are asking for something harder than word index on a CLOB.

The other is what is the actual business use case that would cause you to store a large volume of JSON strings and indicate generalized  searching on key value pairs (recognizing that this kind of search is generally *not* available with audit trails)?

How am I doing as a Product Manager, Rob?

> Let me play product manager here.

No.

> How am I doing as a Product Manager, Rob?

Shall I call ..(Ok, ok I'll shut up)

Product management is an important role in any development shop and it is a valuable skill for a developer to acquire the ability to think like a product manager, if only because it can make it more likely that the things one wants will be scheduled for development.  It is critical when dealing with PSC because the product managers, rightfully so, are the gatekeepers.  This is not to say that there aren't stupid product managers in the world, but we are blessed with some very good ones at PSC.

gus has probably already coded the feature during this discussion

"how hard can it be"

http://en.wikipedia.org/wiki/Top_Gear_challenges#How_hard_can_it_be.3F

On 16 June 2014 16:46, Mike Fechner
wrote:
> AW: json datatype
> Reply by Mike Fechner
> But Rob and Co are doing a great job. So do we need the Dr. playing this
> role just because he's bored? Let them do the job, that have the job...
>
> Von meinem Windows Phone gesendet
> ________________________________
> Von: Thomas Mercer-Hursh
> Gesendet: ‎16.‎06.‎2014 17:42
> An: TU.OE.General@community.progress.com
> Betreff: RE: [Technical Users - OE General] json datatype
>
> RE: json datatype
> Reply by Thomas Mercer-Hursh
>
> Product management is an important role in any development shop and it is a
> valuable skill for a developer to acquire the ability to think like a
> product manager, if only because it can make it more likely that the things
> one wants will be scheduled for development. It is critical when dealing
> with PSC because the product managers, rightfully so, are the gatekeepers.
> This is not to say that there aren't stupid product managers in the world,
> but we are blessed with some very good ones at PSC.
>
> Stop receiving emails on this subject.
>
> Flag this post as spam/abuse.
>
> Stop receiving emails on this subject.
>
> Flag this post as spam/abuse.



--
Julian Lyndon-Smith
IT Director,
dot.r
http://www.dotr.com

"The bitterness of poor quality remains long after the sweetness of
low price is forgotten”

Follow dot.r on http://twitter.com/DotRlimited

If you want the feature in the language, then you need to provide the business use case whether I am asking for it or Rob & Co are doing so.  As I said, I'm not inherently opposed and would think there was a strong argument for word-index on CLOB for many purposes.

As for you, Julian, if it were easy, we would already have word index on CLOB.   I don't yet see a case where this is not sufficient without inventing new datatypes and syntax.

you failed to spot the sarcasm .. TopGear is always saying "how hard
can it be ?" well - often it is *very* hard

Julian

On 16 June 2014 17:24, Thomas Mercer-Hursh
wrote:
> RE: json datatype
> Reply by Thomas Mercer-Hursh
>
> If you want the feature in the language, then you need to provide the
> business use case whether I am asking for it or Rob & Co are doing so. As I
> said, I'm not inherently opposed and would think there was a strong argument
> for word-index on CLOB for many purposes.
>
> As for you, Julian, if it were easy, we would already have word index on
> CLOB. I don't yet see a case where this is not sufficient without
> inventing new datatypes and syntax.
>
> Stop receiving emails on this subject.
>
> Flag this post as spam/abuse.



--
Julian Lyndon-Smith
IT Director,
dot.r
http://www.dotr.com

"The bitterness of poor quality remains long after the sweetness of
low price is forgotten”

Follow dot.r on http://twitter.com/DotRlimited

Ah.

On the newest indexing / jsonb data-type michael.otacoo.com/.../postgres-9-4-feature-highlight-indexing-jsonb

geoffreylitt.com/.../Postgres-BSON.pdf

Julio Vassallo posted an interesting reaction to the peg a couple of days ago, here's a copy:

-------------------------------------------------------------------------------------------------------------------------

And long before the JSON type, Postgres had the hstore type, a kind of unstructured key-value storage.  You could use that type and still do better than MongoDB:

thebuild.com/.../pg-as-nosql-pgday-fosdem-2013.pdf

At the time of that presentation, the JSON type was little more than a Text type with JSON validation.  But Hstore could still give Mongo a run for the money as you can see.

Now in the latest Postgres, JSON has special query operators and much better indexing support so it makes a lot more sense to use it.  And in 9.4, Postgres is now much faster than MongoDB in all categories and of course it doesn't have the insanely bloated indexes of Mongo: obartunov.livejournal.com/175235.html

-----------------------------------------------------------------------------------------------------------------------

Kind regards,

Stefan Houtzager

Houtzager ICT consultancy & development

www.linkedin.com/in/stefanhoutzager

I'm a bit late to the discussion- just back from vacation where the most technical decisions I had to make was what my next beverage was going to be.

To the defense of Thomas, it is very useful if not mandatory to understand the use case(s) for any new feature that is requested. Technically we can do most anything given time and resources, but from a business perspective should we? It is relatively costly to introduce and maintain new data types with special capabilities, so we would want to be certain that there is clear benefit to our user community.

My request is that you formalize your idea/request in the Ideas section of communities: community.progress.com/.../default.aspx. This is where all enhancement requests go, and we ask that you provide as much detail on the use case(s) as possible. We evaluate all submissions and those with merit get moved into our product backlog for consideration as new features in future releases of OpenEdge.

Thanks,

  Rob

Any idea on whether any of the OE Development ideas will ever be at a status other than "Under Review"?

90 ideas in the last 4 months & every one of them is "under review".

I'm sure you will already do this, but your reaction can make another impression. Of course you could pro-actively look for usecases yourself too. Google on "postgres json datatype" usecase and see the first hit:

clarkdave.net/.../what-can-you-do-with-postgresql-and-json

Furthermore I hope you follow groups like the peg and extract the needs there too. Sitting and waiting for extensively enhancement requests to be filled in in a special ideas section (I never did it but posted ideas all around) is not enough to be competitive.

@agent_008: of course it isn't enough. nor is it the only activity we engage in to figure out what to do next.

@andrew.may: the response to these suggestions has been abysmal. on the other hand, we don't plan the work for the next release every day. many times, the best we could do is to say "we will consider it".  it is very unlikely that the response will ever be "brilliant. we will do that tomorrow.".

what kind of response would you want to see from the product managers?

I'd like to see a change to the Ideas section where the default idea status is not "Under Review", because that is inaccurate.  I suggest a default of "Submitted", which could be changed by Product Management to a status of "Under Review" when they actually review it.  

That's a small change, and it wouldn't tell you the outcome of the review, but that's okay for me.  It would still provide some much-needed feedback.  I've submitted many ideas but without feedback, except from other customers, it's hard to justify continuing to do so.

Rob and Company do a lot of listening in many places.  To suggest otherwise is a bit rude.  That doesn't mean that, if you have some favorite bit that you personally would like to make the roadmap, it isn't smart for you to help its chances by describing the business use case and the deficiencies of the work arounds.  That can make it immediately obvious to them why something is important and important in the context of ABL and PSC's business partners, not some theoretical blog on some site somewhere.

@Gus: My gripe would be that without any PM feedback, there seems to be no point in us giving these ideas or voting on other people's ideas. The ideas section has the potential to be a good resource for PSC to gauge what the community wants & to give some feedback to the community, but we're hearing nothing back.

I would prefer an active PM that provided feedback on any request that was getting more than a few votes.  Obviously, there will be internal pressures to keep all plans private/confidential & to promise nothing - how you balance these pressures is up to you. 

On popular but potentially hard-to-implement features (e.g. adding multi-threading to ABL or adding enums), I would like some feedback from the PM on how important these issues are to them - these might then want to be split out into other threads or ideas.

For popular issues that seem like they would be fairly trivial to implement (e.g. "default to no splash screen in dev environment" or "give a compiler warning if a non-void method is missing a RETURN statement"), I would like some feedback on why the issue is nastier than it sounds or an indication of where it fits into PSC priorities

(i.e. if a lot of people complain about a simple to fix niggle, the niggle should probably be fixed sooner rather than later).

[mention:4b133177ec1e4b6f9a6a7832e6f29913:e9ed411860ed4f2ba0265705b8793d05] I agree with your feedback on the ideas statuses and we would really like to be able to have our own "idea status" but unfortunately Ideas is something very new in the platform we're using to run our community and status can't be changed today. We have already reported enhancements requests for that as well as enabling notifications on ideas.

[mention:f822c6bf04534292b3fe04704ddd8dd6:e9ed411860ed4f2ba0265705b8793d05] Does your vendor's site have an Ideas section?  Maybe you can ask for it there  ;)

Thank you for the feedback :)

I think it would also help if there was more shared understanding about the whole product management process.  It isn't as if prioritization is really a continuous process where each new idea is stuck into a priority queue and development simply worked off the top of that queue.  It may have gotten a little more like that with the move to agile development, but there is still going to be a period once or twice a year depending on cycles where they sit down, get estimates on development cost, and balance priority, cost, coherency, synergy, etc. to decide on what happens in the next release ... and then, even that is highly tentative and subject to revision as the work on the release progresses.  Really, the PM team has been quite good about providing snapshots of this process in the Info Exchanges where we get to see what is currently under way, what is clearly/probably around the corner, and what is clearly on the list, but farther out than the next release.  Unfortunately, not everyone gets to go to those sessions and they are the sort of thing one is hesitant to publish without the full recording to provide context and nuance.  Jean, perhaps when you get the webinar software sorted out again, it would be worth considering having a session or two a year per category of on-line Info Exchange so that those not able to attend could listen and contribute.