current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I would like to know if is possible to 'covnert' a json object to a json array to iterate over a mixed set of data.

I have two rows that look like

{Data:{BASE:{B1:0,color:green}}}
{Data:{BASE:[{B1:1,color:red},{B1:0,color:blue}]}}

I would like to extract the B1 val from all this rows, but I am a bit blocked :)

My first try was a json_extract_array, but it fails on the 1st row (not an array). Then my second try was a json_array_length with a case, but that fails at the 1st row (not array)

Can I handle this situation in any way? Basically I need to extract all the rows where B1 > 0 in one of the json array (or object) and maybe return the node that contains B1 > 0.

share|improve this question

2 Answers 2

up vote 1 down vote accepted

Your main problem is you mix the data types under the json -> 'Data' -> 'BASE' path, which cannot be handled easily. I could come up with a solution, but you should fix your schema, f.ex. to only contain arrays at that path.

with v(j) as (
  values (json '{"Data":{"BASE":{"B1":0,"color":"green"}}}'),
         ('{"Data":{"BASE":[{"B1":1,"color":"red"},{"B1":0,"color":"blue"}]}}')
)
select j, node
from v,
lateral (select j #> '{Data,BASE}') b(b),
lateral (select substring(trim(leading E'\x20\x09\x0A\x0D' from b::text) from 1 for 1)) l(l),
lateral (select case
  when l = '{' and (b #>> '{B1}')::numeric > 0 then b
  when l = '[' then (select e from json_array_elements(b) e where (e #>> '{B1}')::numeric > 0 limit 1)
  else null
end) node(node)
where node is not null

SQLFiddle

share|improve this answer
    
Only a good programmer could have written this code. But IMO it is way too smart/hacky. If a dumb programmer (like me?) can't write code to extract data from a certain schema then that schema is wrong. –  Clodoaldo Neto Jul 4 '14 at 10:40
    
@ClodoaldoNeto I agree, fixing the schema is the priority here, but i'm afraid that requires even more smart/hacky ways to do it in SQL (if it can be done at all). –  pozs Jul 4 '14 at 10:50
    
the main problem here is that i can't change how the data get stored. All this data are generated by a 'tostring' of a wrapper (in java) that consist of a macro object, with a linkedlist. if the linkedlist contains 1 element, the data are serialized as jsonobject, if the linkedlist contains > 1 object, then the data are seralized as a json array. The main reason for this 'hack' is that hte code use a tons of (for e : element.getElement("Bau")) and doesn't know if Base is an object or whatever. The only solution will be serialize everything as an array, but will be to hard to fix everything :( –  user3804873 Jul 11 '14 at 8:50
    
seems that my best otpion is to fetch a subset of data and then filter the json on java side probably? –  user3804873 Jul 11 '14 at 8:52
    
@user3804873 yes, fixing it on java side would be the best solution. –  pozs Jul 11 '14 at 8:55
up vote 1 down vote

To return the rows where at least one object has B1 > 0

select *
from t
where true in (
    select (j ->> 'B1')::int > 0
    from json_array_elements (json_column -> 'Data' -> 'BASE') s (j)
)
share|improve this answer
    
This will only work, when all rows contains an array at json_column -> 'Data' -> 'BASE' -- otherwise throws this exception: cannot call json_array_elements on a non-array –  pozs Jul 4 '14 at 10:00
    
@pozs That is true and I will try to fix it but I think there should be consistency in the data, I mean, there should always be an array even an empty one. –  Clodoaldo Neto Jul 4 '14 at 10:03
    
Agreed, but sadly an appropriate UPDATE for this (in pure SQL) is not so easy. –  pozs Jul 4 '14 at 10:08
    
@posz And also sadly my query is not easily fixable. But I will keep it just to show how bad is JSON for data integrity. –  Clodoaldo Neto Jul 4 '14 at 10:10
    
if i want to get multiple records means how i have to write the query? may be like this : select count(*) from articles where 'true in (select (j ->> 'id')::int[] ?| array['1','2'] from json_array_elements (json::json -> 'category_ids') s (j)) –  Jenorish Mar 20 at 13:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.