json datatype - Forum - OpenEdge General - Progress Community
 Forum

json datatype

  • We could use a piece of document database in our RDBMS too, postgres
    has the json datatype. About what why etc:

    http://dba.stackexchange.com/questions/25101/what-can-i-do-with-the-new-json-field
    http://clarkdave.net/2013/06/what-can-you-do-with-postgresql-and-json/

    What do you think?

    --
    Kind regards,

    Stefan Houtzager

    Houtzager ICT consultancy & development

    www.linkedin.com/in/stefanhoutzager

  • 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?

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • My understanding is, that it could be persisted in a form that optimizes access.
    When you store a JSON as a LONGCHAR/LOB it has to be parsed again completely when you access it.
    From: Thomas Mercer-Hursh [mailto:bounce-tamhas@community.progress.com]
    Sent: Samstag, 14. Juni 2014 22:50
    To: TU.OE.General@community.progress.com
    Subject: RE: [Technical Users - OE General] json datatype
     
    Reply by Thomas Mercer-Hursh

    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?

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.

    Architect of the SmartComponent Library and WinKit

    Consultingwerk Ltd.

  • 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?

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • Maybe XML. As there is a parser in the language and it can be processed in the AVM.
     
    DOCX? Where should the advantage be? You don’t manipulate them in the language. You read them from disc in a BLOB, dump them in the DB and back to disc for opening in Word. As DOCX is already zipped, not even compression should bring any advantage.
    From: Thomas Mercer-Hursh [mailto:bounce-tamhas@community.progress.com]
    Sent: Samstag, 14. Juni 2014 23:11
    To: TU.OE.General@community.progress.com
    Subject: RE: [Technical Users - OE General] json datatype
     
    Reply by Thomas Mercer-Hursh

    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?

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.

    Architect of the SmartComponent Library and WinKit

    Consultingwerk Ltd.

  • 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.

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • As irrelevant as defining variables without NO-UNDO? ;-)
     
    From: Thomas Mercer-Hursh [mailto:bounce-tamhas@community.progress.com]
    Sent: Samstag, 14. Juni 2014 23:23
    To: TU.OE.General@community.progress.com
    Subject: RE: [Technical Users - OE General] json datatype
     
    Reply by Thomas Mercer-Hursh

    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.

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.

    Architect of the SmartComponent Library and WinKit

    Consultingwerk Ltd.

  • 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?

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • I have not decided to “pay the performance cost of persisting to disk and …”
     
    There are use cases for storing structured data (aka objects) in the DB without the need to have matching relational structures. Storing JSON is very convenient for that. IMHO with no real alternative (other than XML which is ugly to process in the language). Everything that may make this more efficient is welcome.
     
    I am surprised that this seems such a strange use case for you.
     
    From: Thomas Mercer-Hursh [mailto:bounce-tamhas@community.progress.com]
    Sent: Samstag, 14. Juni 2014 23:31
    To: TU.OE.General@community.progress.com
    Subject: RE: [Technical Users - OE General] json datatype
     
    Reply by Thomas Mercer-Hursh

    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?

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.

    Architect of the SmartComponent Library and WinKit

    Consultingwerk Ltd.

  • 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?

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • 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

    On 15 Jun 2014 17:27, "Thomas Mercer-Hursh" <bounce-tamhas@community.progress.com> wrote:
    Reply by Thomas Mercer-Hursh

    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?

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.

  • 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.

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • 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

    On 15 Jun 2014 17:41, "Thomas Mercer-Hursh" <bounce-tamhas@community.progress.com> wrote:
    Reply by Thomas Mercer-Hursh

    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.

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.

  • You are not getting the point.
     
    If it’s stored in the DB as a character / clob, how do you want to do indexed searches on properties of the JSON object? It’s not possible, unless there is a special data type with special handling – right in the DB.
     
    Or do you still deny the existence of a real overhead when you need to go through 100.000 records (sequentially), build JSON objects from string data and compare a property from the JSON object with a value?
     
     
     
    Von: Thomas Mercer-Hursh [mailto:bounce-tamhas@community.progress.com]
    Gesendet: Sonntag, 15. Juni 2014 18:28
    An: TU.OE.General@community.progress.com
    Betreff: RE: [Technical Users - OE General] json datatype
     
    Reply by Thomas Mercer-Hursh

    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?

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.

    Architect of the SmartComponent Library and WinKit

    Consultingwerk Ltd.

  • 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.

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com