Ecto nested embeds with jsonb

Questions / Help
,
1

Hello, I am having some trouble with nested embeds, I have a jsonb column metadata which looks like this:

  field(:collected_from, :string)
  field(:era, :string)

  embeds_one(:file, File)
  embeds_one(:sync, Sync)
end

But when try to get something from file I cannot, it looks like the File embed is saved as string.
Repo.all(from d in "data", select: fragment("metadata->>'file'")) This returns something like:

"{\"id\": \"63769d9b-fee8-456d-afae-96e8a9efaaa7\", \"line\": 4, \"path\": \"/home/x/Work/EPark/eparkomat/_build/dev/lib/eparkomat/priv/mock_folder/labels/6219321040015.MOV.txt\", \"file_created_time\": \"2018-03-26T12:33:18\", \"file_accessed_time\": \"2018-05-07T10:26:58\", \"file_modified_time\": \"2018-03-26T12:33:18\"}",
"{\"id\": \"9420931f-afa6-42d7-b5b7-7a4bd42a670e\", \"line\": 9, \"path\": \"/home/x/Work/EPark/eparkomat/_build/dev/lib/eparkomat/priv/mock_folder/labels/6219321040015.MOV.txt\", \"file_created_time\": \"2018-03-26T12:33:18\", \"file_accessed_time\": \"2018-05-07T10:26:58\", \"file_modified_time\": \"2018-03-26T12:33:18\"}",
"{\"id\": \"2a362e2f-9eb4-4462-bff6-42f21e8e47e4\", \"line\": 3, \"path\": \"/home/x/Work/EPark/eparkomat/_build/dev/lib/eparkomat/priv/mock_folder/labels/8689819590011.MOV.txt\", \"file_created_time\": \"2018-05-07T10:25:19\", \"file_accessed_time\": \"2018-05-07T10:25:19\", \"file_modified_time\": \"2018-05-07T10:25:19\"}"]

But when I do Repo.all(from d in "data", select: fragment("metadata->>'file.line'")) it just returns nils

Any ideas?

2

Ok so what I ended up doing is something like the following

fragment("((metadata->>'file')::json->'line')::text::int") seems to work :slight_smile:

Or what @jtranin suggested fragment("(metadata#>>'{file, line}')::integer ASC")

2 Likes