Is there a way to query empty json colum in postgresql db and to compare it to other json objects?












0















i have the same problem described in this question.
PostgreSQL: compare jsons



but, i want a way to do it in rails, i have metadata column, that i have to query every time a record inserted. so, i have to compare between the value that will be inserted and the content i have, also, that column maybe empty.



what i have tried to use the json::text, but that means that i have to make a query for each setting of the json keys and values.



    ## for checking the empty query and if not empty, i loop through the json objects

    if metadata.to_s == '{}' || metadata.to_s == ''

      gears = gear_query.where("metadata::text = ?", metadata.to_s)

    else

      gears = gear_query.each.collect{ |g| g if g.eq_metadata?(metadata) }.compact

    end



    ## looping through all the shapes of the json objects 

    ## and calling diff function to recognize if the objects are different or not



  def eq_metadata?(metadata)

    src_mtd = self.metadata.sort_by { |k, v| k }.to_h

    new_mtd = metadata.sort_by { |k, v| k }.to_h

    src_keys = src_mtd.keys.each.map(&:to_sym)

    new_keys = new_mtd.keys.each.map(&:to_sym)

    ((src_keys == new_keys) && (src_mtd.values == new_mtd.values))

  end


i think that there is a better way, or that i am missing something with my code. my question not about raw sql queries, it is about a way to compare json objects' content stored in the database with a json object in hand.
the way i took is to get all the json objects out of the database then comparing them to the json object in hand... is there another way??






ruby-on-rails json postgresql activerecord







share|improve this question

























  • Possible duplicate of Rails raw SQL example

    – Raymond Nijland
    Jan 1 at 14:16











  • @RaymondNijland, thanks alot but my question not about raw sql queries, it is about a way to compare json objects' content stored in the database with a json object in hand. the way i took is to get all the json objects out of the database then comparing them to the json object in hand... is there another way??

    – Socertis
    Jan 2 at 12:30











  • ", it is about a way to compare json objects' content stored in the database with a json object in hand" "is there another way?? " Probably not i think executiing a raw query on the active record framework is the only way.

    – Raymond Nijland
    Jan 2 at 15:51













  • Okay. But the question you say that mine is a duplicate from is about raw queries in general, not about json objects comparison as in my question.

    – Socertis
    Jan 2 at 16:48











  • not is a duplicate i said a possible duplicate besides the comment is automated when you flag a post as duplication vote close.. "is about raw queries in general, not about json objects comparison as in my question" think for yourself please what programming requires and edit the duplication answer so following for example.... sql = "select ('{"x":"a", "y":"b"}')::json::text = ('{"y":"b", "x":"a"}')::json::text" records_array = ActiveRecord::Base.connection.execute(sql); ... or with a table select * from table WHERE <json_column>::json::text = ('{"y":"b", "x":"a"}')::json::text

    – Raymond Nijland
    Jan 2 at 17:37
















0















i have the same problem described in this question.
PostgreSQL: compare jsons



but, i want a way to do it in rails, i have metadata column, that i have to query every time a record inserted. so, i have to compare between the value that will be inserted and the content i have, also, that column maybe empty.



what i have tried to use the json::text, but that means that i have to make a query for each setting of the json keys and values.



    ## for checking the empty query and if not empty, i loop through the json objects

    if metadata.to_s == '{}' || metadata.to_s == ''

      gears = gear_query.where("metadata::text = ?", metadata.to_s)

    else

      gears = gear_query.each.collect{ |g| g if g.eq_metadata?(metadata) }.compact

    end



    ## looping through all the shapes of the json objects 

    ## and calling diff function to recognize if the objects are different or not



  def eq_metadata?(metadata)

    src_mtd = self.metadata.sort_by { |k, v| k }.to_h

    new_mtd = metadata.sort_by { |k, v| k }.to_h

    src_keys = src_mtd.keys.each.map(&:to_sym)

    new_keys = new_mtd.keys.each.map(&:to_sym)

    ((src_keys == new_keys) && (src_mtd.values == new_mtd.values))

  end


i think that there is a better way, or that i am missing something with my code. my question not about raw sql queries, it is about a way to compare json objects' content stored in the database with a json object in hand.
the way i took is to get all the json objects out of the database then comparing them to the json object in hand... is there another way??






ruby-on-rails json postgresql activerecord







share|improve this question

























  • Possible duplicate of Rails raw SQL example

    – Raymond Nijland
    Jan 1 at 14:16











  • @RaymondNijland, thanks alot but my question not about raw sql queries, it is about a way to compare json objects' content stored in the database with a json object in hand. the way i took is to get all the json objects out of the database then comparing them to the json object in hand... is there another way??

    – Socertis
    Jan 2 at 12:30











  • ", it is about a way to compare json objects' content stored in the database with a json object in hand" "is there another way?? " Probably not i think executiing a raw query on the active record framework is the only way.

    – Raymond Nijland
    Jan 2 at 15:51













  • Okay. But the question you say that mine is a duplicate from is about raw queries in general, not about json objects comparison as in my question.

    – Socertis
    Jan 2 at 16:48











  • not is a duplicate i said a possible duplicate besides the comment is automated when you flag a post as duplication vote close.. "is about raw queries in general, not about json objects comparison as in my question" think for yourself please what programming requires and edit the duplication answer so following for example.... sql = "select ('{"x":"a", "y":"b"}')::json::text = ('{"y":"b", "x":"a"}')::json::text" records_array = ActiveRecord::Base.connection.execute(sql); ... or with a table select * from table WHERE <json_column>::json::text = ('{"y":"b", "x":"a"}')::json::text

    – Raymond Nijland
    Jan 2 at 17:37














0












0








0








i have the same problem described in this question.
PostgreSQL: compare jsons



but, i want a way to do it in rails, i have metadata column, that i have to query every time a record inserted. so, i have to compare between the value that will be inserted and the content i have, also, that column maybe empty.



what i have tried to use the json::text, but that means that i have to make a query for each setting of the json keys and values.



    ## for checking the empty query and if not empty, i loop through the json objects

    if metadata.to_s == '{}' || metadata.to_s == ''

      gears = gear_query.where("metadata::text = ?", metadata.to_s)

    else

      gears = gear_query.each.collect{ |g| g if g.eq_metadata?(metadata) }.compact

    end



    ## looping through all the shapes of the json objects 

    ## and calling diff function to recognize if the objects are different or not



  def eq_metadata?(metadata)

    src_mtd = self.metadata.sort_by { |k, v| k }.to_h

    new_mtd = metadata.sort_by { |k, v| k }.to_h

    src_keys = src_mtd.keys.each.map(&:to_sym)

    new_keys = new_mtd.keys.each.map(&:to_sym)

    ((src_keys == new_keys) && (src_mtd.values == new_mtd.values))

  end


i think that there is a better way, or that i am missing something with my code. my question not about raw sql queries, it is about a way to compare json objects' content stored in the database with a json object in hand.
the way i took is to get all the json objects out of the database then comparing them to the json object in hand... is there another way??






ruby-on-rails json postgresql activerecord







share|improve this question
















i have the same problem described in this question.
PostgreSQL: compare jsons



but, i want a way to do it in rails, i have metadata column, that i have to query every time a record inserted. so, i have to compare between the value that will be inserted and the content i have, also, that column maybe empty.



what i have tried to use the json::text, but that means that i have to make a query for each setting of the json keys and values.



    ## for checking the empty query and if not empty, i loop through the json objects

    if metadata.to_s == '{}' || metadata.to_s == ''

      gears = gear_query.where("metadata::text = ?", metadata.to_s)

    else

      gears = gear_query.each.collect{ |g| g if g.eq_metadata?(metadata) }.compact

    end



    ## looping through all the shapes of the json objects 

    ## and calling diff function to recognize if the objects are different or not



  def eq_metadata?(metadata)

    src_mtd = self.metadata.sort_by { |k, v| k }.to_h

    new_mtd = metadata.sort_by { |k, v| k }.to_h

    src_keys = src_mtd.keys.each.map(&:to_sym)

    new_keys = new_mtd.keys.each.map(&:to_sym)

    ((src_keys == new_keys) && (src_mtd.values == new_mtd.values))

  end


i think that there is a better way, or that i am missing something with my code. my question not about raw sql queries, it is about a way to compare json objects' content stored in the database with a json object in hand.
the way i took is to get all the json objects out of the database then comparing them to the json object in hand... is there another way??






ruby-on-rails json postgresql activerecord




ruby-on-rails json postgresql activerecord






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Jan 2 at 12:28







Socertis

















asked Jan 1 at 10:57









SocertisSocertis

146




146













  • Possible duplicate of Rails raw SQL example

    – Raymond Nijland
    Jan 1 at 14:16











  • @RaymondNijland, thanks alot but my question not about raw sql queries, it is about a way to compare json objects' content stored in the database with a json object in hand. the way i took is to get all the json objects out of the database then comparing them to the json object in hand... is there another way??

    – Socertis
    Jan 2 at 12:30











  • ", it is about a way to compare json objects' content stored in the database with a json object in hand" "is there another way?? " Probably not i think executiing a raw query on the active record framework is the only way.

    – Raymond Nijland
    Jan 2 at 15:51













  • Okay. But the question you say that mine is a duplicate from is about raw queries in general, not about json objects comparison as in my question.

    – Socertis
    Jan 2 at 16:48











  • not is a duplicate i said a possible duplicate besides the comment is automated when you flag a post as duplication vote close.. "is about raw queries in general, not about json objects comparison as in my question" think for yourself please what programming requires and edit the duplication answer so following for example.... sql = "select ('{"x":"a", "y":"b"}')::json::text = ('{"y":"b", "x":"a"}')::json::text" records_array = ActiveRecord::Base.connection.execute(sql); ... or with a table select * from table WHERE <json_column>::json::text = ('{"y":"b", "x":"a"}')::json::text

    – Raymond Nijland
    Jan 2 at 17:37



















  • Possible duplicate of Rails raw SQL example

    – Raymond Nijland
    Jan 1 at 14:16











  • @RaymondNijland, thanks alot but my question not about raw sql queries, it is about a way to compare json objects' content stored in the database with a json object in hand. the way i took is to get all the json objects out of the database then comparing them to the json object in hand... is there another way??

    – Socertis
    Jan 2 at 12:30











  • ", it is about a way to compare json objects' content stored in the database with a json object in hand" "is there another way?? " Probably not i think executiing a raw query on the active record framework is the only way.

    – Raymond Nijland
    Jan 2 at 15:51













  • Okay. But the question you say that mine is a duplicate from is about raw queries in general, not about json objects comparison as in my question.

    – Socertis
    Jan 2 at 16:48











  • not is a duplicate i said a possible duplicate besides the comment is automated when you flag a post as duplication vote close.. "is about raw queries in general, not about json objects comparison as in my question" think for yourself please what programming requires and edit the duplication answer so following for example.... sql = "select ('{"x":"a", "y":"b"}')::json::text = ('{"y":"b", "x":"a"}')::json::text" records_array = ActiveRecord::Base.connection.execute(sql); ... or with a table select * from table WHERE <json_column>::json::text = ('{"y":"b", "x":"a"}')::json::text

    – Raymond Nijland
    Jan 2 at 17:37

















Possible duplicate of Rails raw SQL example

– Raymond Nijland
Jan 1 at 14:16





Possible duplicate of Rails raw SQL example

– Raymond Nijland
Jan 1 at 14:16













@RaymondNijland, thanks alot but my question not about raw sql queries, it is about a way to compare json objects' content stored in the database with a json object in hand. the way i took is to get all the json objects out of the database then comparing them to the json object in hand... is there another way??

– Socertis
Jan 2 at 12:30





@RaymondNijland, thanks alot but my question not about raw sql queries, it is about a way to compare json objects' content stored in the database with a json object in hand. the way i took is to get all the json objects out of the database then comparing them to the json object in hand... is there another way??

– Socertis
Jan 2 at 12:30













", it is about a way to compare json objects' content stored in the database with a json object in hand" "is there another way?? " Probably not i think executiing a raw query on the active record framework is the only way.

– Raymond Nijland
Jan 2 at 15:51







", it is about a way to compare json objects' content stored in the database with a json object in hand" "is there another way?? " Probably not i think executiing a raw query on the active record framework is the only way.

– Raymond Nijland
Jan 2 at 15:51















Okay. But the question you say that mine is a duplicate from is about raw queries in general, not about json objects comparison as in my question.

– Socertis
Jan 2 at 16:48





Okay. But the question you say that mine is a duplicate from is about raw queries in general, not about json objects comparison as in my question.

– Socertis
Jan 2 at 16:48













not is a duplicate i said a possible duplicate besides the comment is automated when you flag a post as duplication vote close.. "is about raw queries in general, not about json objects comparison as in my question" think for yourself please what programming requires and edit the duplication answer so following for example.... sql = "select ('{"x":"a", "y":"b"}')::json::text = ('{"y":"b", "x":"a"}')::json::text" records_array = ActiveRecord::Base.connection.execute(sql); ... or with a table select * from table WHERE <json_column>::json::text = ('{"y":"b", "x":"a"}')::json::text

– Raymond Nijland
Jan 2 at 17:37





not is a duplicate i said a possible duplicate besides the comment is automated when you flag a post as duplication vote close.. "is about raw queries in general, not about json objects comparison as in my question" think for yourself please what programming requires and edit the duplication answer so following for example.... sql = "select ('{"x":"a", "y":"b"}')::json::text = ('{"y":"b", "x":"a"}')::json::text" records_array = ActiveRecord::Base.connection.execute(sql); ... or with a table select * from table WHERE <json_column>::json::text = ('{"y":"b", "x":"a"}')::json::text

– Raymond Nijland
Jan 2 at 17:37












0






active

oldest

votes











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53994883%2fis-there-a-way-to-query-empty-json-colum-in-postgresql-db-and-to-compare-it-to-o%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























0






active

oldest

votes








0






active

oldest

votes









active

oldest

votes






active

oldest

votes
















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid …



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53994883%2fis-there-a-way-to-query-empty-json-colum-in-postgresql-db-and-to-compare-it-to-o%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







-

Popular posts from this blog

Monofisismo

Image
Monofisismo (do grego: monos - "único, singular" e physis - "natureza") é o ponto de vista cristológico que defende que, depois da união do divino e do humano na encarnação histórica, Jesus Cristo, como encarnação do Filho ou Verbo ( Logos ) de Deus, teria apenas uma única "natureza", a divina, e não uma síntese de ambas. O monofisismo é contraposto pelo diofisismo (ou "diafisismo"), que defende que Jesus preservou em si as duas naturezas. Historicamente, o monofisismo se refere primordialmente à posição dos que (especialmente no Egito e, em menor grau, na Síria) rejeitaram as decisões do Concílio de Calcedônia em 451 (o quarto concílio ecumênico). Os membros mais moderados entre eles, porém, defendem a teologia "miafisita", que se tornou a oficial para as Igrejas Ortodoxas Orientais. Muitos ortodoxos orientais, porém, rejeitam essa classificação, mesmo como um termo genérico, mas ele é amplamente utilizado na literatura históri...
Read more

Angular Downloading a file using contenturl with Basic Authentication

Image
0 I have a contentUrl when user clicks on that,file should be downloaded. If I try to browse the contentUrl in the browser it asks for Userid and password.I believe this is Basic Authentication.I dont want user to enter UserId and Password so I am passing it Authorization(Please see below code). Url looks some thing like this: http://XXX.XXX.XX.XXX:8080/flowable-rest/service/runtime/tasks/90875/attachments/90555/content let username : string = 'admin'; let pwd : string = 'test'; let headers = new Headers(); headers.set('Accept', 'text/json'); headers.append('Content-Type', 'application/json'); headers.set('Access-Control-Allow-Origin', '*'); headers.set('Access-Control-Allow-Methods','POST, GET, OPTIONS, PUT, DELETE'); head...
Read more

Olmecas

Image
Monumento 1, uma das quatro cabeças colossais encontradas em La Venta, com altura aproximada de 3 metros Olmecas é a designação do povo e da civilização que estiveram na origem da antiga cultura pré-colombiana da Mesoamérica e que se desenvolveram nas regiões tropicais do centro-sul do atual México durante o pré-clássico, próximo de onde hoje estão localizados os estados mexicanos de Veracruz e Tabasco, no Istmo de Tehuantepec, numa zona designada área nuclear olmeca. A cultura olmeca floresceu nesta região aproximadamente entre 1500 e 400 a.C., [ 1 ] e crê-se que tenha sido a civilização-mãe de todas as civilizações mesoamericanas que se desenvolveram posteriormente. [ 2 ] No entanto, desconhece-se a sua exacta filiação étnica, ainda que existam numerosas hipóteses colocadas para tentar resolver esta questão. O etnónimo olmeca foi cunhado pelos arqueólogos do século XX, e não devem confundir-se com os muito posteriores olmecas-xicalancas que ocuparam vários locais do México...
Read more