Send netdisco-users mailing list submissions to
netdisco-users@lists.sourceforge.net
To subscribe or unsubscribe via the World Wide Web, visit
https://lists.sourceforge.net/lists/listinfo/netdisco-users
or, via email, send a message with subject or body 'help' to
netdisco-users-requ...@lists.sourceforge.net
You can reach the person managing the list at
netdisco-users-ow...@lists.sourceforge.net
When replying, please edit your Subject line so it is more specific
than "Re: Contents of netdisco-users digest..."
Today's Topics:
1. Re: Cisco IMEI tracker report for cellular services
(Christian Ramseyer)
2. Re: Cisco IMEI tracker report for cellular services (Muris)
3. Re: Cisco IMEI tracker report for cellular services
(Oliver Gorwits)
--- Begin Message ---
On 17.09.2024 19:14, Oliver Gorwits wrote:
I have no idea how to fix this, sorry. I guess it could be worked around
by refactoring the query not to use the PostgreSQL jsonb operator "?".
WHERE____
(custom_fields \? 'c3gGsmNetwork' AND
json_array_length((custom_fields ->> 'c3gGsmNetwork')::json) > 0)____
OR____
(custom_fields \? 'c3gImei' AND json_array_length((custom_fields
->> 'c3gImei')::json) > 0)____
Which should be easy here, you can just leave out the part before the
AND and still get the same result (untested)
Cheers
Christian
--- End Message ---
--- Begin Message ---
Thanks for the tip I looked at alternatives and I managed to get it working
using a different method avoiding the ? operator it uses json_path_exists
function to check instead.
You could have this as alternate option for others that it doesn’t work for also
SELECT COALESCE(NULLIF(dns,''), NULLIF(name,''), '') AS devname, ip,
CASE
WHEN jsonb_path_exists(custom_fields::jsonb, '$.c3gGsmNetwork') THEN
ARRAY(SELECT
jsonb_array_elements_text((custom_fields->>'c3gGsmNetwork')::jsonb))::text[]
ELSE NULL
END AS carrier,
CASE
WHEN jsonb_path_exists(custom_fields::jsonb, '$.c3gImei') THEN
ARRAY(SELECT
jsonb_array_elements_text((custom_fields->>'c3gImei')::jsonb))::text[]
ELSE NULL
END AS imei
FROM device
WHERE
(jsonb_path_exists(custom_fields::jsonb, '$.c3gGsmNetwork') AND
custom_fields->>'c3gGsmNetwork' IS NOT NULL AND custom_fields->>'c3gGsmNetwork'
<> '[]')
OR
(jsonb_path_exists(custom_fields::jsonb, '$.c3gImei') AND
custom_fields->>'c3gImei' IS NOT NULL AND custom_fields->>'c3gImei' <> '[]')
ORDER BY ip ASC;
On 18/9/2024, 06:09, "Christian Ramseyer" <ramse...@netnea.com
<mailto:ramse...@netnea.com>> wrote:
On 17.09.2024 19:14, Oliver Gorwits wrote:
>
> I have no idea how to fix this, sorry. I guess it could be worked around
> by refactoring the query not to use the PostgreSQL jsonb operator "?".
>
> WHERE____
>
> (custom_fields \? 'c3gGsmNetwork' AND
> json_array_length((custom_fields ->> 'c3gGsmNetwork')::json) > 0)____
>
> OR____
>
> (custom_fields \? 'c3gImei' AND json_array_length((custom_fields
> ->> 'c3gImei')::json) > 0)____
>
Which should be easy here, you can just leave out the part before the
AND and still get the same result (untested)
Cheers
Christian
--- End Message ---
--- Begin Message ---
Hi Muris, that's great news, glad you found a working query!
On Wed, 18 Sept 2024 at 01:35, Muris <alcat...@gmail.com> wrote:
> Thanks for the tip I looked at alternatives and I managed to get it
> working using a different method avoiding the ? operator it uses
> json_path_exists function to check instead.
>
> You could have this as alternate option for others that it doesn’t work
> for also
>
> SELECT COALESCE(NULLIF(dns,''), NULLIF(name,''), '') AS devname, ip,
> CASE
> WHEN jsonb_path_exists(custom_fields::jsonb, '$.c3gGsmNetwork')
> THEN
> ARRAY(SELECT
> jsonb_array_elements_text((custom_fields->>'c3gGsmNetwork')::jsonb))::text[]
>
> ELSE NULL
> END AS carrier,
> CASE
> WHEN jsonb_path_exists(custom_fields::jsonb, '$.c3gImei') THEN
> ARRAY(SELECT
> jsonb_array_elements_text((custom_fields->>'c3gImei')::jsonb))::text[]
> ELSE NULL
> END AS imei
> FROM device
> WHERE
> (jsonb_path_exists(custom_fields::jsonb, '$.c3gGsmNetwork') AND
> custom_fields->>'c3gGsmNetwork' IS NOT NULL AND
> custom_fields->>'c3gGsmNetwork' <> '[]')
> OR
> (jsonb_path_exists(custom_fields::jsonb, '$.c3gImei') AND
> custom_fields->>'c3gImei' IS NOT NULL AND custom_fields->>'c3gImei' <> '[]')
> ORDER BY ip ASC;
>
> On 18/9/2024, 06:09, "Christian Ramseyer" <ramse...@netnea.com <mailto:
> ramse...@netnea.com>> wrote:
>
>
>
>
>
>
> On 17.09.2024 19:14, Oliver Gorwits wrote:
> >
> > I have no idea how to fix this, sorry. I guess it could be worked around
> > by refactoring the query not to use the PostgreSQL jsonb operator "?".
> >
> > WHERE____
> >
> > (custom_fields \? 'c3gGsmNetwork' AND
> > json_array_length((custom_fields ->> 'c3gGsmNetwork')::json) > 0)____
> >
> > OR____
> >
> > (custom_fields \? 'c3gImei' AND json_array_length((custom_fields
> > ->> 'c3gImei')::json) > 0)____
> >
>
>
> Which should be easy here, you can just leave out the part before the
> AND and still get the same result (untested)
>
>
> Cheers
> Christian
>
>
>
>
>
>
> _______________________________________________
> Netdisco mailing list
> netdisco-users@lists.sourceforge.net
> https://sourceforge.net/p/netdisco/mailman/netdisco-users/
--- End Message ---
_______________________________________________
Netdisco mailing list - Digest Mode
netdisco-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/netdisco-users
