Json.Document
JSON read into M: objects become records, arrays lists, and numbers, texts, logicals and null as they are.
Group: Files and documents. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Json.Document(jsonText as any, optional encoding as nullable number) as any| Parameter | Type | Description |
|---|---|---|
jsonText | any | The JSON: a file's bytes (File.Contents, Web.Contents) or text. |
encoding | nullable number optional | The bytes' text encoding, as for Text.FromBinary; TextEncoding.Utf8 unless you say otherwise. A byte order mark at the start wins. |
Returns
any. A record, a list or a single value, as the JSON's top level is.
Remarks
- A record keeps the object's fields in their order. To make a table of a list of objects, use
Table.FromRecords(MissingField.UseNull for objects that lack a field) or expand it step by step. - Texts that look like dates stay texts: change their type in a later step.
- JSON that can't be read is a DataFormat.Error.
Examples
A file
Json.Document(File.Contents("C:\Data\people.json"))[people = {[name = "Ann", age = 31, tags = {"a", "b"}], [name = "Bo", age = null, active = true]}, count = 2]Each person as a row
Table.FromRecords(Json.Document(File.Contents("C:\Data\people.json"))[people], {"name", "age"}, MissingField.UseNull)| name | age |
|---|---|
| Ann | 31 |
| Bo | null |
JSON written as text
Json.Document("{""order"": 1042, ""shop"": ""Oslo"", ""lines"": [{""sku"": ""A-1"", ""qty"": 2}, {""sku"": ""B-7"", ""qty"": 1}]}")[order = 1042, shop = "Oslo", lines = {[sku = "A-1", qty = 2], [sku = "B-7", qty = 1]}]Related functions
Table.FromRecords, File.Contents, Web.Contents, Record.ToTable.