Search code examples
sqldjangopostgresqlmetabase

Extract JSON content in Metabase SQL query

Using: Django==2.2.24, Python=3.6, PostgreSQL is underlying DB
Working with Django ORM, I can easily make all sort of queries, but I started using Metabase, and my SQL might be a bit rusty.

The problem:
I am trying to get a count of the items in a list, under a key in a dictionary, stored as a JSONField:

from django.db import models
from jsonfield import JSONField

class MyTable(models.Model):
  data_field = JSONField(blank=True, default=dict)

Example of the dictionary stored in data_field:

{..., "my_list": [{}, {}, ...], ...}

Under "my_list" key, the value stored is a list, which contains a number of other dictionaries.
In Metabase, I am trying to get a count for the number of dictionaries in the list, but even more basic things, none of which work.

Some stuff I tried:
Attempt:

SELECT COUNT(elem->'my_list') as my_list_count
FROM my_table, json_object_keys(data_field:json) AS elem

Error:

ERROR: syntax error at or near ":" Position: 226

Attempt:

SELECT ARRAY_LENGTH(elem->'my_list') as my_list_count
FROM my_table, JSON_OBJECT_KEYS(data_field:json) AS elem

Error:

ERROR: syntax error at or near ":" Position: 233

Attempt:

SELECT JSON_ARRAY_LENGTH(data_field->'my_list'::json)
FROM my_table

Error:

ERROR: invalid input syntax for type json Detail: Token "my_list" is invalid. Position: 162 Where: JSON data, line 1: my_list

Attempt:

SELECT ARRAY_LENGTH(JSON_QUERY_ARRAY(data_field, '$.my_list'))
FROM my_table

Error:

ERROR: function json_query_array(text, unknown) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 140

Basically, I think the issue is that I am using the wrong signatures (most of the time) in the methods I am trying to use.

I used this query to make sure I can at least get the keys from the dictionary:

SELECT JSON_OBJECT_KEYS(data_field::json)
FROM my_table

I was not able to use JSON_OBJECT_KEYS() without adding the ::json cast, I was getting this error:

ERROR: function json_object_keys(text) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 127

But with the json cast, I am getting all the keys as intended.


Thank you for taking a look!


EDIT:
I also found this interesting article with different solution but none of the solutions worked.
Also seen this SO post which did not help.

Solution

  • Ok, after some more digging around, I found this article, which had the correct format/syntax.

    This code is what I used to fetch the list from the JSON object successfully:

    select data_field::json->'my_list' as the_list
from my_table

    Then, I used json_array_length() to get the number of elements:

    select json_array_length(data_field::json->'my_list') as number_of_elements
from my_table

    All done! :)

    EDIT:
    I just found the reason to this whole shenanigan. In the code (which goes years back) we used this package:

    jsonfield==1.0.3

    And used this way:

    from jsonfield import JSONField

    The issue is that in the background, Postgres saves the data as a string, so it needs to be cast into a JSON. Later Django introduced its own JSONField, which stores data as you would expect, without a need to cast:

    from django.contrib.postgres.fields import JSONField