How to select a certain key from Json string

Question

I am trying to get the value of "accountBalance", but beside of that I get also a bunch of unwanted "0". It seems that I get the "0" on every element that has no "accountBalance" key . How could I select only those elements that contains the key "accountBalance".

My Code:

var resultOpTrades = JsonConvert.DeserializeObject<RootObject>(result);//Deserialize Json
var pricelist = resultOpTrades.transactions.Select(p => p.accountBalance).ToList().Select(s => Convert.ToDouble(s)).ToList();
pricelist.ForEach(Console.WriteLine);

JSON:

{"transactions": [
{
  "type": "CLIENT_CONFIGURE",
  "marginRate": "0.02",
  "alias": "Test USD",
  "id": "2",
  "userID": 4455670,
  "accountID": "101-004-4455670-004",
  "batchID": "1",
  "requestID": "1789786643428780285",
  "time": "2018-01-22T13:01:57.930423995Z"
},
{
  "accountBalance": "2000.0000",
  "type": "TRANSFER_FUNDS",
  "amount": "2000.0000000000",
  "fundingReason": "ADJUSTMENT",
  "id": "3",
  "userID": 4455670,
  "accountID": "101-004-4455670-004",
  "batchID": "3",
  "requestID": "1735743448013647784",
  "time": "2018-01-22T13:02:24.580177329Z"
},
{
  "type": "MARKET_ORDER",
  "instrument": "EUR_JPY",
  "units": "-2000",
  "timeInForce": "FOK",
  "positionFill": "DEFAULT",
  "reason": "CLIENT_ORDER",
  "id": "4",
  "userID": 4455670,
  "accountID": "101-004-4455670-004",
  "batchID": "4",
  "requestID": "60404387589188847",
  "time": "2018-01-22T13:06:12.138121604Z"
},
{
  "type": "ORDER_FILL",
  "orderID": "4",
  "instrument": "EUR_JPY",
  "units": "-2000",
  "price": "135.627",
  "pl": "0.0000",
  "financing": "0.0000",
  "commission": "0.0000",
  "accountBalance": "2000.0000",
  "gainQuoteHomeConversionFactor": "0.009022013713",
  "lossQuoteHomeConversionFactor": "0.009023071995",
  "guaranteedExecutionFee": "0.0000",
  "halfSpreadCost": "0.1353",
  "fullVWAP": "135.627",
  "reason": "MARKET_ORDER",
  "tradeOpened": {
    "price": "135.627",
    "tradeID": "5",
    "units": "-2000",
    "guaranteedExecutionFee": "0.0000",
    "halfSpreadCost": "0.1353"
  },
  "fullPrice": {
    "closeoutBid": "135.627",
    "closeoutAsk": "135.642",
    "timestamp": "2018-01-22T13:05:56.780436649Z",
    "bids": [
      {
        "price": "135.627",
        "liquidity": "10000000"
      }
    ],
    "asks": [
      {
        "price": "135.642",
        "liquidity": "10000000"
      }
    ]
  },
  "id": "5",
  "userID": 4455670,
  "accountID": "101-004-4455670-004",
  "batchID": "4",
  "requestID": "60404387589188847",
  "time": "2018-01-22T13:06:12.138121604Z"
},
{
  "type": "MARKET_ORDER",
  "instrument": "EUR_JPY",
  "units": "2000",
  "timeInForce": "FOK",
  "positionFill": "REDUCE_ONLY",
  "reason": "TRADE_CLOSE",
  "tradeClose": {
    "units": "ALL",
    "tradeID": "5"
  },
  "id": "6",
  "userID": 4455670,
  "accountID": "101-004-4455670-004",
  "batchID": "6",
  "requestID": "60404387832520278",
  "time": "2018-01-22T13:07:10.544407912Z"
},],"lastTransactionID": "22083"}

The result I get: 0, 2000, 0, 2000, 0,

It seems like your JSON contains a list of RootObject but some properties are ignored if it is null [JsonProperty(NullValueHandling = NullValueHandling.Ignore)]. That's why some properties are missing in your JSON item. So you can either check if it is null or zero before using it. — Farshan
– Farshan, Commented Mar 7, 2020 at 12:24

Pavel Anikhouski · Accepted Answer · 2020-03-12 09:44:25Z

1

You can use Json.Linq to parse your JSON into JObject, then iterate a transactions property and get an accountBalance values

var json = JObject.Parse(result));
foreach (var item in json["transactions"])
{
    if (item["accountBalance"] != null)
    {
        Console.WriteLine(item["accountBalance"].Value<double>());
    }
}

edited Mar 12, 2020 at 9:44

answered Mar 7, 2020 at 11:48

Pavel Anikhouski

23.6k12 gold badges66 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pavel Anikhouski Over a year ago

@DianaM00nshine Welcome, you can approve an answer, if it solves your question:)

Farshan · Accepted Answer · 2020-03-07 12:27:58Z

0

Filter the pricelist with the value zero using a Where in Linq

var resultOpTrades = JsonConvert.DeserializeObject<RootObject>(result);//Deserialize Json
var pricelist = resultOpTrades.transactions.Where(x => x.accountBalance != 0).Select(p => Convert.ToDouble(p.accountBalance)).ToList();
pricelist.ForEach(Console.WriteLine);

answered Mar 7, 2020 at 12:27

Farshan

4465 silver badges15 bronze badges

Comments

claudiom248 · Accepted Answer · 2020-03-07 12:50:09Z

Hello @Diana M00nshine,

From what I understood, you want to take only the transactions that has the accountBalance field set.

First, I would suggest you to create this class to reflect the schema of your json array field called "transactions".

class Transaction
{
    public string accountBalance { get; set; }
    public string type { get; set; }
    public string marginRate { get; set; }
    public string alias { get; set; }
    public string id { get; set; }
    public int userID { get; set; }
    public string accountID { get; set; }
    public string batchID { get; set; }
    public string requestID { get; set; }
    public string time { get; set; }
}

To get all the transactions that have the property accountBalance defined in the JSON string you could do as showed below:

Parse the json string
Take the transactions token and cast it to an IEnumerable
Then for each item inside the transactions token, you convert them to an object of the class Transaction that I have included in the source code above.
Cast to a list of Transaction objects

var json = JObject.Parse(jsonString);
List<Transaction> transactionsWithAccountBalance = json["transactions"].AsJEnumerable()
                                                        .Where(t => t["accountBalance"] != null)
                                                        .Select(t => t.ToObject<Transaction>())
                                                        .ToList();

If you want only the transactions that not only have the key accountBalance set in the json but also different than null or empty, you could update the where condition as follow

.Where(t => t["accountBalance"] != null && string.IsNullOrEmpty(t["accountBalance"].Value<string>()))

Then, if you want a list with all the account balances of your transactions, you could do:

var priceList = transactionsWithAccountBalance.Select(t => t.accountBalance).ToList();

I've included this little test class to let you easily try my solution. You can see the in the list will be present only transactions that have the interested key in the json file set.

class Program
{
    static void Main(string[] args)
    {
        string jsonString = @"{
            ""transactions"": [
            {
                ""type"": ""CLIENT_CONFIGURE"",
                ""marginRate"": ""0.02"",
                ""alias"": ""Test USD"",
                ""id"": ""2"",
                ""userID"": 4455670,
                ""accountID"": ""101-004-4455670-004"",
                ""batchID"": ""1"",
                ""requestID"": ""1789786643428780285"",
                ""time"": ""2018-01-22T13:01:57.930423995Z""
            },
            {
                ""accountBalance"": ""2000.0000"",
                ""type"": ""TRANSFER_FUNDS"",
                ""amount"": ""2000.0000000000"",
                ""fundingReason"": ""ADJUSTMENT"",
                ""id"": ""3"",
                ""userID"": 4455670,
                ""accountID"": ""101-004-4455670-004"",
                ""batchID"": ""3"",
                ""requestID"": ""1735743448013647784"",
                ""time"": ""2018-01-22T13:02:24.580177329Z""
            },
            {
                ""type"": ""MARKET_ORDER"",
                ""instrument"": ""EUR_JPY"",
                ""units"": ""-2000"",
                ""timeInForce"": ""FOK"",
                ""positionFill"": ""DEFAULT"",
                ""reason"": ""CLIENT_ORDER"",
                ""id"": ""4"",
                ""userID"": 4455670,
                ""accountID"": ""101-004-4455670-004"",
                ""batchID"": ""4"",
                ""requestID"": ""60404387589188847"",
                ""time"": ""2018-01-22T13:06:12.138121604Z""
            },
            {
                ""type"": ""ORDER_FILL"",
                ""orderID"": ""4"",
                ""instrument"": ""EUR_JPY"",
                ""units"": ""-2000"",
                ""price"": ""135.627"",
                ""pl"": ""0.0000"",
                ""financing"": ""0.0000"",
                ""commission"": ""0.0000"",
                ""accountBalance"": ""2000.0000"",
                ""gainQuoteHomeConversionFactor"": ""0.009022013713"",
                ""lossQuoteHomeConversionFactor"": ""0.009023071995"",
                ""guaranteedExecutionFee"": ""0.0000"",
                ""halfSpreadCost"": ""0.1353"",
                ""fullVWAP"": ""135.627"",
                ""reason"": ""MARKET_ORDER"",
                ""tradeOpened"": {
                ""price"": ""135.627"",
                ""tradeID"": ""5"",
                ""units"": ""-2000"",
                ""guaranteedExecutionFee"": ""0.0000"",
                ""halfSpreadCost"": ""0.1353""
                },
                ""fullPrice"": {
                ""closeoutBid"": ""135.627"",
                ""closeoutAsk"": ""135.642"",
                ""timestamp"": ""2018-01-22T13:05:56.780436649Z"",
                ""bids"": [
                    {
                    ""price"": ""135.627"",
                    ""liquidity"": ""10000000""
                    }
                ],
                ""asks"": [
                    {
                    ""price"": ""135.642"",
                    ""liquidity"": ""10000000""
                    }
                ]
                },
                ""id"": ""5"",
                ""userID"": 4455670,
                ""accountID"": ""101-004-4455670-004"",
                ""batchID"": ""4"",
                ""requestID"": ""60404387589188847"",
                ""time"": ""2018-01-22T13:06:12.138121604Z""
            },
            {
                ""type"": ""MARKET_ORDER"",
                ""instrument"": ""EUR_JPY"",
                ""units"": ""2000"",
                ""timeInForce"": ""FOK"",
                ""positionFill"": ""REDUCE_ONLY"",
                ""reason"": ""TRADE_CLOSE"",
                ""tradeClose"": {
                ""units"": ""ALL"",
                ""tradeID"": ""5""
                },
                ""id"": ""6"",
                ""userID"": 4455670,
                ""accountID"": ""101-004-4455670-004"",
                ""batchID"": ""6"",
                ""requestID"": ""60404387832520278"",
                ""time"": ""2018-01-22T13:07:10.544407912Z""
            }
            ],
            ""lastTransactionID"": ""22083""
        }";

        var json = JObject.Parse(jsonString);
        List<Transaction> transactionsWithAccountBalance = json["transactions"].AsJEnumerable()
                                                                .Where(t => t["accountBalance"] != null)
                                                                .Select(t => t.ToObject<Transaction>())
                                                                .ToList();

        var priceList = transactionsWithAccountBalance.Select(t => t.accountBalance).ToList();

        Console.ReadKey();
    }
}

Don't forget to add these namespaces in order to compile the program class:

using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Linq;

Collectives™ on Stack Overflow

How to select a certain key from Json string

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related