View `acoustic.receiver_logs_data`:cannot call jsonb_each_text on a non-object

[PietrH]

I believe there is something wrong with the view: acoustic.receiver_logs_data

During testing for #254, I noticed a query that fails only when LIMIT is set high, or when missing or LIMIT ALL, after eliminating all other options, I'm left with the conclusion that something must be wrong with the view, maybe when iterating over the JSON object (log_data ?)

To replicate:

SELECT * FROM acoustic.receiver_logs_data LIMIT 5000

I got it to fail with LIMIT as low as 1000.

For #254, it is important that larger sets of diagnostic data can be returned as well.

@Stijn-VLIZ , could you have a look at this and let me know if I can provide any further information.

[Stijn-VLIZ]

There are indeed issues with the view and/or data. some of the data in the receiver logs are an array instead of object. I can exclude all these arrays in the view. But then it turns out not all json's are formed correctly.

I added two extra filters to remove arrays and malformed json's could you try again?

[Stijn-VLIZ]

due to a postgress issue you will however get an error if your request returns no data.

[PietrH]

Thanks for getting to this so quickly, I'm testing now

[PietrH]

I'm not able to get this query to run, it times out:

SELECT
  log.deployment_fk AS deployment_id,
  receiver.receiver AS receiver_id,
  log.datetime AS datetime,
  log.record_type,
  log.log_data
FROM
  acoustic.receiver_logs_data AS log
  LEFT JOIN acoustic.deployments AS dep
    ON log.deployment_fk = dep.id_pk
  LEFT JOIN acoustic.receivers AS receiver
    ON dep.receiver_fk = receiver.id_pk
WHERE
deployment_fk IN (6025)
LIMIT ALL

I do quite a bit of wrangling after the result to get into shape for users. Quite a bit of this could probably be done more efficiently in the View.

Here are some examples of some output tables the function is returning: https://github.com/inbo/etn/issues/254#issuecomment-2341142514

Specifically I want the JSON format parsed out to a wide format, and I then make it shorter again by reducing it to a single record per deployment_id, record_type, datetime combo and filling empty values.

[PietrH]

@Stijn-VLIZ Could you have a look at the query above for the view? It's not super urgent