SQLC - how to retrieve data stored as JSONB

Question

I have a simple table that has a field JSONB:

CREATE TABLE IF NOT EXISTS "test_table" (
    "id" text NOT NULL,
    "user_id" text NOT NULL,
    "content" jsonb NOT NULL,
    "create_time" timestamptz NOT NULL,
    "update_time" timestamptz NOT NULL,
    PRIMARY KEY ("id")
);

I used a simple query to generate boilerplate with SQLC.

-- name: GetTestData :one
SELECT * FROM test_table
WHERE id = $1 LIMIT 1;

But the content property gets generated as json.RawMessage.

type TestTable struct {
    ID          string          `json:"id"`
    UserId      string          `json:"user_id"`
    Content     json.RawMessage `json:"content"`
    CreateTime  time.Time       `json:"create_time"`
    UpdateTime  time.Time       `json:"update_time"`
}

Here's a sample of the JSON that is stored inside the content column:

{
  "static": {
    "product": [
      {
        "id": "string",
        "elements": {
          "texts": [
            {
              "id": "string",
              "value": "string"
            }
          ],
          "colors": [
            {
              "id": "string",
              "value": "string"
            }
          ],
          "images": [
            {
              "id": "string",
              "values": [
                {
                  "id": "string",
                  "value": "string"
                }
              ]
            }
          ]
        }
      }
    ]
  },
  "dynamic": {
    "banner": [
      {
        "id": "string",
        "elements": {
          "texts": [
            {
              "id": "string",
              "value": "string"
            }
          ],
          "colors": [
            {
              "id": "string",
              "value": "string"
            }
          ],
          "images": [
            {
              "id": "string",
              "values": [
                {
                  "id": "string",
                  "value": "string"
                }
              ]
            }
          ]
        }
      }
    ]
  }
}

Nested properties inside Static or Dynamic are arrays.

The content property should contain a nested object, and I can't seem to extract the data inside it. json.Unrmarshall() seems get only the top level properties. Is there a way to cast map[string]interface{} to content or to help SQLC generate the property as interface instead of RawMessage?

I tried to solve this just unmarshalling the raw message like so:

var res map[string]json.RawMessage
if err := json.Unmarshal(testingData.Content, &res); err != nil {
    return nil, status.Errorf(codes.Internal, "Serving data err %s", err)
}

var static pb.Static
if err := json.Unmarshal(res["Static"], &static); err != nil {
    return nil, status.Errorf(codes.Internal, "Static data err %s", err)
}
var dynamic pb.Dynamic
if err := json.Unmarshal(res["Dynamic"], &dynamic); err != nil {
    return nil, status.Errorf(codes.Internal, "Dynamic data err %s", err)
}

I'm doing something wrong when unmarshalling the payload but I can't figure out what exactly.

Here's a sample playground: go.dev/play/p/9e7a63hNMEA

Please edit the question and include the JSON in question. The code SQLC generates should just return whatever is in the database (you can confirm this with something like `fmt.Printf("%s\n", testingData.Content)`). — Brits, Oct 31 '22 at 03:25
Sorry I thought it would clutter the question. Done, the JSON is included from the print statement as suggested. — Mido, Oct 31 '22 at 03:52

Brits · Accepted Answer · 2022-10-31T05:26:08.647

1

Your JSON contains static and dynamic keys. You are parsing into a map[string]json.RawMessage and then trying to retrieve Static and Dynamic from the map (note the capitalisation).

Fix the map keys (i.e. json.Unmarshal(res["static"], &static))and your code will probably work. A better solution might be to check if the keys exist before attempting to unmarshal them.

edited Oct 31 '22 at 05:26

answered Oct 31 '22 at 05:03

Brits

14,829
2
18
31

Thanks for your help, at least I uncovered why I couldn't get the first part printed on console. capitalisation... But I'm still having the same issue. Here's a sample on the playground: https://go.dev/play/p/9e7a63hNMEA – Mido Nov 02 '22 at 06:27
Sorry I'm not really understanding the issue. If it's just a matter of dumping the data to the console marshal it to JSON, implement [`Stringer`](https://go.dev/play/p/52fci5AhDim), or use something like [go-spew](https://github.com/davecgh/go-spew) to pretty-print the data. – Brits Nov 02 '22 at 19:27
Thanks for the reply. Sorry my example was probably confusing. Printing the result was a way to show the issue I'm having. I might be missing something basic here, but as context, this is an gRPC API. So I'm trying to return the "static" or "dynamic" portion of the json. When I try to "convert" the RawMessage to Static or Dynamic, I get nothing. The return of the API is { static: content: [], product: [] } – Mido Nov 02 '22 at 20:55
I think that's probably another question because the info required differs from the current question - as per [this example](https://go.dev/play/p/52fci5AhDim) your code is parsing the JSON (at least the elements I checked). Because the protobuf plug-in does not allow you to customise JSON tags sometimes the process of going from JSON -> protobuf structs can be a bit manual. I don't believe your issue is at all related to sqlc (which was its original focus). – Brits Nov 02 '22 at 22:39
Oh, I see. I'll close this then. Thank you for your time and help! – Mido Nov 02 '22 at 23:30

SQLC - how to retrieve data stored as JSONB

1 Answers1